Unique Count by Month

C

Cassie

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
 
B

Bob Phillips

Try this array formula

=SUM(--(FREQUENCY(IF(C1:C20="Jul",MATCH(B1:B20,B1:B20,0)),ROW(INDIRECT("1:"&ROWS(B1:B20))))>0))
 
B

Bob Umlas

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))
 
C

Cassie

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
 
B

Bob Phillips

That would be

=SUM(SIGN(FREQUENCY(IF(C2:C10171="Jul",MATCH(B2:B10171,B2:B10171,0)),ROW(INDIRECT("1:"&ROWS(B2:B10171)-ROW(B2)+1)))))

and make sure that you array-enter it
 
C

Cassie

Thanks very much - that works a treat
Cassie

Bob Phillips said:
That would be

=SUM(SIGN(FREQUENCY(IF(C2:C10171="Jul",MATCH(B2:B10171,B2:B10171,0)),ROW(INDIRECT("1:"&ROWS(B2:B10171)-ROW(B2)+1)))))

and make sure that you array-enter it


--

HTH

Bob




.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count unique names in a list 5
counting unique values conditionally 7
Sum of Numbers of a Rolling 6 month Period range 0
Counting unique values in multiple columns 4
Array counting 3
Unique Value Count in 3 Columns Using Search Criteria 0
taking a count 3
Update cell value based on name and specific date 0

Top