Thread: counting duplicates once

I have two columns, One with names (column C) another with multiple dates (column d).
I would like to count the number of names once with in a certain month.

column c Column D
Ivie 7/1/2010
wood 7/1/2010
Taylor 7/2/2010
Ivie 7/5/2010
Baker 7/7/2010
Ivie 7/10/2010

From the example above, result of number of names should be 4, but I am getting a result of 1 when I use the below formula.


I am using the frequency function, but it is not giving me the correct result.

(column F) data array =IF((YEAR(D2)=(2010))*(MONTH(D2)= 7)),MATCH(C2,$C$2:$C$950,0))
(Column G) bins array =ROW(C2)-ROW($C$2:$C$950)+1
=FREQUENCY(F2:F950, G2:G950)
(column H) if i typed in =SUM(IF(H2:H86,1)) the value result is 1
if I type it in as =SUMIF(H2:H86,1) the value results in 17 (the correct value)

When I put it all together

=SUM(IF(FREQUENCY(IF((YEAR(D2:D950)=(2010))*(MONTH(D2:D950)=(7)),MATCH(C2:C950,C2:C950,0)), ROW(C2:C950)-ROW($C$2:$C$950)+1), 1))

The results is 1.

Do I have an error in the formula or is this an excel 2007 issue?

Your Bins Array is a bit off... try:

=SUM(IF(FREQUENCY(IF((YEAR(D2:D950)=(2010))*(MONTH(D2:D950)=(7)),MATCH(C2:C950,C2:C950,0)), ROW(C2:C950)-ROW(C2)+1),1))

I guess I assumed you knew that this was an array formula and therefore has to be confirmed with CTRL+SHIFT+ENTER not just ENTER.

Can you show how you get 53?

If I combine first and last names, I get more (70)... probably because you have more unique combinations of first/last names... i.e several people have same lastname or several have same first name...

This is the formula:

=SUM(IF(FREQUENCY(IF((YEAR(D2:D950)=(2010))*(MONTH(D2:D950)=(7)),MATCH(B2:B950&C2:C950,B2:B950&C2:C950,0)), ROW(C2:C950)-ROW(C2)+1),1))

Confirmed with cTRL+SHIFT+ENTER