|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 24th, 2010, 05:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Don't Count Duplicates
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 
|
|
#2
April 24th, 2010, 05:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Don't Count Duplicates
Criteria start in E2 and down
In F2: =SUM(N(FREQUENCY(IF(County=E2,MATCH(Name,Name,)),M ATCH(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
|
|
#3
April 24th, 2010, 06:39 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Don't Count Duplicates
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" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
