Formula - Count distinct / Countif??

I need a formula to count based on the criteria: Account Number (must only count distinct/unique), Category, and Date.

As per the example below, there are 3 lines with Account FGS00109 in Category HHH and with a Go Live Data as 01/12/12.

In the table (required result) under Category HHH and Date Dec-12 there should only be a count of 1 as there is only 1 unique account number.
The current formula I’m using only counts the number of results within the date range and does not recognise distinct account numbers.

Does anyone know how I can amend my formula or create a new one?


A B C
Account Number Category Active Date
FSG00109 HHH 01/12/2012
FSG00109 HHH 01/12/2012
FSG00109 HHH 01/12/2012
FSG00324 GGG 01/02/2013
FSG00324 GGG 01/02/2013
FSG00292 FTC 01/12/2012
FSG00144 HHH 01/03/2013

Required result

Nov-12 Dec-12 Jan-13 Feb-13 Mar-13
HHH 0 1 1 1 2
GGG 0 0 0 1 1
FTC 0 1 1 1 1


Current result (not counting distinct account numbers only)

Nov-12 Dec-12 Jan-13 Feb-13 Mar-13
HHH 0 3 3 3 4
GGG 0 0 0 2 2
FTC 0 1 1 1 1

This is the formula I'm using for the Current Result
=SUM(($B$2:$B$8="HHH")*($C$2:$C$8>=VALUE("01/11/2012"))*($C$2:$C$8<=VALUE("31/12/2012")))

Any help would be great.

Thank you

Does this work for you?

=SUMPRODUCT(--($B$2:$B$8="HHH"),--(IFERROR(MATCH($A$2:$A$8,INDEX(LEFT($A$2:$A$8,LEN($A$2:$A$8)*($B$2:$B$8="HHH")*($C$2:$C$8>=DATE(2012,11,1))*($C$2:$C$8<=DATE(2012,12,31))),0),0)+1=ROW($A$2:$A$8),0)),--($C$2:$C$8>=DATE(2012,11,1)),--($C$2:$C$8<=DATE(2012,12,31)))

As an array formula.

No sorry, just get #NAME?

Thanks for the response though.

Sorry, I've just realised you're using Excel 2003, which means that you haven't got the IFERROR function.

Try this instead:

=SUMPRODUCT(--($B$2:$B$8="HHH"),--(IF(ISNA(MATCH($A$2:$A$8,INDEX(LEFT($A$2:$A$8,LEN($A$2:$A$8)*($B$2:$B$8="HHH")*($C$2:$C$8>=DATE(2012,11,1))*($C$2:$C$8<=DATE(2012,12,31))),0),0)),-10,MATCH($A$2:$A$8,INDEX(LEFT($A$2:$A$8,LEN($A$2:$A$8)*($B$2:$B$8="HHH")*($C$2:$C$8>=DATE(2012,11,1))*($C$2:$C$8<=DATE(2012,12,31))),0),0))+1=ROW($A$2:$A$8)),--($C$2:$C$8>=DATE(2012,11,1)),--($C$2:$C$8<=DATE(2012,12,31)))