Count all unique occurrences given a criteria

[nm1234]

Given the data set:

Name Date Amount
Steve Jan 1 $5
Phil Jan 1 $7
Phil Feb 1 $5
John Feb 1 $3

I want to determine new customers on a monthly basis. So In January I should have 2 new customers Steve and Phil but in February I should only have one new customer John.

January February
2 1

I've tried using a frequency match array formula but the problem is when I narrow the data set by month, the match statement is only looking at February rows so it thinks Phil is a unique (new) customer.

Any help is appreciated!!

 

[Domenic]

First, make sure that all dates are true date values. Then try the following...

A1:C5

Name	Date	Amount
Steve	01-Jan	$5
Phil	01-Jan	$7
Phil	01-Feb	$5
John	01-Feb	$3

<TBODY>
</TBODY>

E1:F1

01-Jan

01-Feb

<TBODY>
</TBODY>

E2, confirmed with CONTROL+SHIFT+ENTER, copied across:

=SUM(IF(FREQUENCY(IF($B$2:$B$5=E$1,IF(ISNA(MATCH($A$2:$A$5,IF($B$2:$B$5 < E$1,$A$2:$A$5),0)),MATCH("~"&$A$2:$A$5,$A$2:$A$5&"",0))),ROW($A$2:$A$5)-ROW($A$2)+1)>0,1))<E$1,$A$2:$A$5),0)),MATCH("~"&$A$2:$A$5,$A$2:$A$5&"",0))),ROW($A$2:$A$5)-ROW($A$2)+1)>

Hope this helps!</E$1,$A$2:$A$5),0)),MATCH("~"&$A$2:$A$5,$A$2:$A$5&"",0))),ROW($A$2:$A$5)-ROW($A$2)+1)>