Counts of unique values with multiple criteria

Hi,

I'm trying to count the unique number of dates a sale was made for a specific promotion. For instance, in the example below, the Tree Sale would have a count of 2 - even though 3 out of 4 attemps resulted in sales, the sales occured only on 2 days: the 5th and the 6th. Similarly, the Bush Sale would have a count of 1 because both sales were made on a single day: the 8th.

Promo	Attempt	Sold	Date
Tree Sale	    1	$100 	3/5/2011
Tree Sale	    2	$0 	3/5/2011
Tree Sale	    3	$200 	3/6/2011
Tree Sale	    4	$75 	3/6/2011
Bush Sale	    5	$80 	3/8/2011
Bush Sale	    6	$0 	3/8/2011
Bush Sale	    7	$125 	3/8/2011
Bush Sale	    8	$0 	3/8/2011

I could create a table with the promo names as column headers, and all possible dates as row headers, and then use sumproduct formulas. But since I'm looking at a year's worth of dates, and scores of promos, this doesn't seem feasible. Is there a more elegant solution?

Thanks,
ChristiaanV

Hi, in example column A product, column D dates

=SUMPRODUCT(($A$1:$A$8="Tree Sale")*(MATCH($A$1:$A$8&$D$1:$D$8,$A$1:$A$8&$D$1:$D$8,0)=ROW($D$1:$D$8)))

Regards

Thanks, but it's not working for me. I realize I didn't set up the problem correctly.

I have a summary worksheet that lists each promo in column A:

Tree Sale
Bush Sale
Shrub Sale

I have another worksheet that lists each sale (the bit about not counting sales for $0 was wrong on my part). I just want to count each sale attempt per unique date, so:

Promo	Attempt	Date
Tree Sale	    1	3/5/2011
Tree Sale	    2	3/5/2011
Tree Sale	    3	3/6/2011
Tree Sale	    4	3/6/2011
Bush Sale	    5	3/8/2011
Bush Sale	    6	3/8/2011
Bush Sale	    7	3/8/2011
Bush Sale	    8	3/8/2011

So in my first worksheet, column B should count the unique date instances for the promo in column A, so that:

Tree Sale     2
Bush Sale     1
Shrub Sale    0

What should my formula be for Column B?

Thanks,
ChristiaanV

Never mind, I'm good. I solved it a different way. Thanks for the help

I first created a column D on my data worksheet which combined the date and promo as text

=A2&C2

I then created a column E on the data worksheet to count each first occurance of this text,
which is the same as counting each unique instance of this text

=IF(COUNTIF(D$2:D2,D2)>1,"",1))

Finally, on my summary worksheet I created a column B, so that for each promo

=SUMIF(Data!A$2:A$8,A2,Data!E$2:E:$8)

which summed the count for each unique date and promo combination for the selected promo.