counting unique items within categories - but EXCLUDING blank cell

[Btaylor64]

Good morning!
About a week ago, I received an amazing formula on this website, for
counting the number of unique items within different categories.
Specifcally, my problem was:

ITEM CATEGORY
apple A
apple A
bannana A
bannana B
bannana B

So I wanted Excel to figure out that there are 2 unique items in
Category A ("apple" and "bannana"), and only one in Category B ("bannana").
The solution I was given was:


=SUM(IF(FREQUENCY(IF(CategoryRange="Category",MATCH"~"&ItemRange,ItemRange&"",0)),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1))

This is a fantastic solution and it works perfrectly - except that it
also counts blank cells as a unique entry, too!
Is there a way to keep Excel from counting blank cells as a unique item
in each category?

Thanks again for the help you guys are providing on this amazing
website!!
-Brett

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(CategoryRange="Category",IF(ItemRange<>"",MATCH("~"&
ItemRange,ItemRange&"",0))),ROW(ItemRange)-MIN(ROW(ItemRange))+1),1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Btaylor64]

Domenic,
Looks like there's a parentheses issue in the formula below? I tried
to troubleshoot this using the colored parenthese font that Excel provides,
but no luck. All the combinations of parentheses placement I've tried give
me errors...
Any thoughts?
Gratefully,
-Brett

 

[Domenic]

The formula as I posted it looks fine. I tried it just to be sure and
had no problems. Did you copy/paste the formula? Try typing it out
manually instead of copy/paste.

 

[Btaylor64]

Domenic,
You're absolutely correct; now it works fine! Not sure what I was
doing wrong before. Thanks again for your help!
-Brett

 

[Domenic]

You're very welcome! Thanks for the feedback!