Distinct Count

[pickslides]

Hi I am looking to do a distinct count of products purchased. Then distinct count of products purchased > 0

Sheet attached, MQ

[Tony Valko]

Are the product codes really numbers as demonstrated in your file?

[pickslides]

Fair Qn, yes they are.

MQ

[Tony Valko]

Try these array formulas**:

I6:

=SUM(IF(FREQUENCY(IF(A$2:A$95=H6,B$2:B$95),B$2:B$95),1))

J6:

=SUM(IF(FREQUENCY(IF((A$2:A$95=H6)*(C$2:C$95>0),B$2:B$95),B$2:B$95),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select I6:J6 and copy down as needed.

[pickslides]

Thx tony, that is working well. Is there a restriction on the number of records I can search through.

I.e I have edited your formula to =SUM(IF(FREQUENCY(IF(A$2:A$23000=H6,B$2:B$23000),B$2:B$23000),1))

and it returns N/A

[pickslides]

All good, I had some rubbish data in there holding up the process!

[Tony Valko]

Good deal. Thanks for the feedback!