Counts of unique values with multiple criteria

[ChristiaanV]

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.

Please Login or Register  to view this content.

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

[canapone]

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

[ChristiaanV]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

What should my formula be for Column B?

Thanks,
ChristiaanV

[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.

[canapone]

Hi,
thanks for the feedback: I've adapted the formula to the new elements you shared

=SUMPRODUCT((Data!$A$1:$A$8=A2)*(MATCH(Data!$A$1:$A$8&Data!$D$1:$D$8,Data!$A$1:$A$8&Data!$D$1:$D$8,0)=ROW($A$1:$A$8)))

The formula should do the same job without the helper column.

I prefer using an helper column - your solution- instead of a heavier formula, but if you have to work on a database where you could have same date and different product, output could be misleading.

Disclaimer: I'm not familiar with the data which you work.

Regards