I have a list of orders, grouped by customer.
I'm trying to count the unique number of orders per customer. Which the following formula works fine: =SUMPRODUCT((range<>"")/COUNTIF(range,range&""))
I've subtotalled my list, but that just tells me customer x has 7 orders, my SUMPRODUCT formula tell me he has 3 unique orders in the list of 7. That's fine, but I don't want to type my SUMPRODUCT formula in for each customer and have to alter the number of rows it applies to. That would take all day.
Some customers have one order, others a few and some many.
Can I get the range bit to figure out the number of rows it needs to apply to, or start/stop where there is a blank row. Having subtotalled it there are blank rows.
Example:
Order1 Customer abc
Order1 Customer abc
Order2 Customer abc
Subtotal 3
Order4 Customer xyz
Order5 Customer xyz
Subtotal 2
So Customer abc has three orders, two of them unique. Customer xyz has two orders, two of them unique. Id like the SUMPRODUCT formula on the same row as the SUBTOTAL ones and not have to edit it to cope with the varying number of rows.
Thanks.
Bookmarks