MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Don't Count Duplicates

• Follow

```Here's a subset of 3 colunms of data:

Zip Code   Name       County
42125      John         Franklin
42156      John         Fairfield
43123      Bill            Fairfield
45612      Jane         Fairfield
45126      Jane         Franklin
49856      Jane         Fairfield
45895      Chris        Fairfiled
46289      Chris        Fairfield
42194      Chris        Fairfield
42312      Chris        Franklin

I am looking for a formual to count the number of people assigned to each
Territory.

The answer based on thes sample data above would be:

Fairfield  - 4
Franklin - 3

Thank you
```
 0
Reply Utf 4/24/2010 4:01:01 PM

```Criteria start in E2 and down

In F2: =SUM(N(FREQUENCY(IF(County=E2,MATCH(Name,Name,)),MATCH(Name,Name,))>0))

ctrl+shift+enter, not just enter
copy down as far as needed

"Johnny" wrote:

> Here's a subset of 3 colunms of data:
>
> Zip Code   Name       County
> 42125      John         Franklin
> 42156      John         Fairfield
> 43123      Bill            Fairfield
> 45612      Jane         Fairfield
> 45126      Jane         Franklin
> 49856      Jane         Fairfield
> 45895      Chris        Fairfiled
> 46289      Chris        Fairfield
> 42194      Chris        Fairfield
> 42312      Chris        Franklin
>
> I am looking for a formual to count the number of people assigned to each
> Territory.
>
> The answer based on thes sample data above would be:
>
> Fairfield  - 4
> Franklin - 3
>
> Thank you
```
 0
Reply Utf 4/24/2010 4:52:01 PM

```Try this array formula**.

B2:B11 = Name
C2:C11 = County

E2 = Fairfield
E3 = Franklin

Array entered** in F2 and copied down to F3:

=SUM(IF(FREQUENCY(IF(C\$2:C\$11=E2,MATCH(B\$2:B\$11,B\$2:B\$11,0)),ROW(B\$2:B\$11)-MIN(ROW(B\$2:B\$11))+1),1))

Assumes no empty cells in the Name range B2:B11.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

"Johnny" <Johnny@discussions.microsoft.com> wrote in message
news:9A1A48A4-9E53-41A5-996C-A47273288F22@microsoft.com...
> Here's a subset of 3 colunms of data:
>
> Zip Code   Name       County
> 42125      John         Franklin
> 42156      John         Fairfield
> 43123      Bill            Fairfield
> 45612      Jane         Fairfield
> 45126      Jane         Franklin
> 49856      Jane         Fairfield
> 45895      Chris        Fairfiled
> 46289      Chris        Fairfield
> 42194      Chris        Fairfield
> 42312      Chris        Franklin
>
> I am looking for a formual to count the number of people assigned to each
> Territory.
>
> The answer based on thes sample data above would be:
>
> Fairfield  - 4
> Franklin - 3
>
> Thank you

```
 0
Reply T 4/24/2010 5:39:51 PM

2 Replies
578 Views

(page loaded in 0.096 seconds)

Similiar Articles:

7/27/2012 12:48:27 AM