Hi I am looking to do a distinct count of products purchased. Then distinct count of products purchased > 0
Sheet attached, MQ
Hi I am looking to do a distinct count of products purchased. Then distinct count of products purchased > 0
Sheet attached, MQ
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.
Are the product codes really numbers as demonstrated in your file?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Fair Qn, yes they are.
MQ
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.
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
All good, I had some rubbish data in there holding up the process!
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks