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

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

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

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



.

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



.

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



.

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



.



.