Prev: 2003 PIvotTable Calculated Fields
Next: Add text to cliparts
From: Johnny on 24 Apr 2010 12:01
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
From: Teethless mama on 24 Apr 2010 12:52
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
From: T. Valko on 24 Apr 2010 13:39
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(a)discussions.microsoft.com> wrote in message
news:9A1A48A4-9E53-41A5-996C-A47273288F22(a)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


 | 
Pages: 1
Prev: 2003 PIvotTable Calculated Fields
Next: Add text to cliparts