| Home |
| Search |
| Today's Posts |
|
|
|
#1
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
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
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count duplicates once | Excel Discussion (Misc queries) | |||
| Count Duplicates | Excel Discussion (Misc queries) | |||
| Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
| Count Duplicates | Excel Discussion (Misc queries) | |||
| count a group of numbers but do not count duplicates | Excel Worksheet Functions | |||
Hybrid Mode
