| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Count by Month
I needed to count all the unique names by month. Names in Col B and the
months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Count by Month
Try this array formula
=SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(INDIRECT("1:"&ROWS(B1:B20))))0)) -- HTH Bob "Cassie" wrote in message ... I needed to count all the unique names by month. Names in Col B and the months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Count by Month
Since ROWS(B1:B20) is always 20, why not:
SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(1:20))0)) "Bob Phillips" wrote in message ... Try this array formula =SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(INDIRECT("1:"&ROWS(B1:B20))))0)) -- HTH Bob "Cassie" wrote in message ... I needed to count all the unique names by month. Names in Col B and the months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Count by Month
Bob
Thanks but that didn't work. Can I do this via a pivot table? Thanks Cassie "Bob Phillips" wrote: Try this array formula =SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(INDIRECT("1:"&ROWS(B1:B20))))0)) -- HTH Bob "Cassie" wrote in message ... I needed to count all the unique names by month. Names in Col B and the months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Count by Month
Bob thanks
I got a #value! error my data runs from row 1 header; row 2 - 10171 is the actual data col b are the months col c are the names Cassie "Bob Umlas" wrote: Since ROWS(B1:B20) is always 20, why not: SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(1:20))0)) "Bob Phillips" wrote in message ... Try this array formula =SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(INDIRECT("1:"&ROWS(B1:B20))))0)) -- HTH Bob "Cassie" wrote in message ... I needed to count all the unique names by month. Names in Col B and the months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Count by Month
That would be
=SUM(SIGN(FREQUENCY(IF(C2:C10171="Jul",MATCH(B2:B1 0171,B2:B10171,0)),ROW(INDIRECT("1:"&ROWS(B2:B1017 1)-ROW(B2)+1))))) and make sure that you array-enter it -- HTH Bob "Cassie" wrote in message ... Bob thanks I got a #value! error my data runs from row 1 header; row 2 - 10171 is the actual data col b are the months col c are the names Cassie "Bob Umlas" wrote: Since ROWS(B1:B20) is always 20, why not: SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(1:20))0)) "Bob Phillips" wrote in message ... Try this array formula =SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(INDIRECT("1:"&ROWS(B1:B20))))0)) -- HTH Bob "Cassie" wrote in message ... I needed to count all the unique names by month. Names in Col B and the months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Count by Month
Thanks very much - that works a treat
Cassie "Bob Phillips" wrote: That would be =SUM(SIGN(FREQUENCY(IF(C2:C10171="Jul",MATCH(B2:B1 0171,B2:B10171,0)),ROW(INDIRECT("1:"&ROWS(B2:B1017 1)-ROW(B2)+1))))) and make sure that you array-enter it -- HTH Bob "Cassie" wrote in message ... Bob thanks I got a #value! error my data runs from row 1 header; row 2 - 10171 is the actual data col b are the months col c are the names Cassie "Bob Umlas" wrote: Since ROWS(B1:B20) is always 20, why not: SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(1:20))0)) "Bob Phillips" wrote in message ... Try this array formula =SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)) ,ROW(INDIRECT("1:"&ROWS(B1:B20))))0)) -- HTH Bob "Cassie" wrote in message ... I needed to count all the unique names by month. Names in Col B and the months are in col c. the names appear multiple times in the file at least once in each month - I only want to count John Smith once in January but his name appears 4 times in January Thanks Cassie . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count unique dates within each unique ID | Excel Worksheet Functions | |||
| count month when date is in day/month/year format | Excel Worksheet Functions | |||
| Filter out unique additions month to month | Excel Discussion (Misc queries) | |||
| Count unique | Excel Worksheet Functions | |||
| Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Linear Mode
