Count Unique Text Strings in a Column, Based on Criteria from a Different Column

SlikRick welcome to the forum.

With data limited to those 8 rows try.

Formula:

Please Login or Register  to view this content.

This ARRAY FORMULA seems to work:
*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please Login or Register  to view this content.

RE #DIV/0!, in the "standard" version of your formula, the divisor is NEVER zero, as each element appears at least once. This ceases to be true if you add a secondary condition which some elements don't satisfy.

When I try Leelnich's formula, I am getting the Div / Zero issue. I have a good feeling it's blank related.

How do I adjust Leelnich's formula to account for potential blanks in Column A and Column B?

=SUMPRODUCT(IF($A$1:$A$8="A",1/COUNTIFS(A1:A8,"A",$B$1:$B$8,$B$1:$B$8)))

That is because COUNTIFS(A1:A8,"A",$B$1:$B$8,$B$1:$B$8) is returning FALSE in the array where there are no "A"s. Reciprocation coerces those into 0s. 1/0 is #DIV0! error.

The one I posted would return those errors too if there where blank cells in B1:B8.

tRY

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Is it still CTRL + SHIFT + ENTER for a Mac?

Also. Does the Array Feature still work on Google Docs?

This ARRAY FORMULA handles the blanks:

Please Login or Register  to view this content.

Of course, Ankur's formula (POST #9) is the best option. It's shorter AND faster.

Originally Posted by SlikRick

I tried adjusting Dave's formula from this:

=SUMPRODUCT(($A$1:$A$8="A")/COUNTIF($B$1:$B$8,$B$1:$B$8))

To this:

=SUMPRODUCT(($A$1:$A$8="A")/COUNTIF($B$1:$B$8,$B$1:$B$8&""))

And I got 2.010799136. The correct answer is 3. So I'm not sure what Excel is doing. But it feels like we're moving in the right direction.

That is because COUNTIF is assigning a value to the empty strings.

Try

=SUMPRODUCT((($A$1:$A$8="A")*($B$1:$B$8<>""))/COUNTIF($B$1:$B$8,$B$1:$B$8&""))

I should have refreshed before posting. Lee's does the same as this one. This one does not have to be array entered.

Originally Posted by leelnich

Dave, this fails if A shares any values with B.

So it does! Nice catch.

This should cover all the bases ... I hope.

=SUMPRODUCT((($A$1:$A$8="A")*($B$1:$B$8<>"")*($A$1:$A$8<>""))/COUNTIFS($A$1:$A$8,$A$1:$A$8&"",$B$1:$B$8,$B$1:$B$8&""))

Originally Posted by FlameRetired

=SUMPRODUCT((($A$1:$A$8="A")*($B$1:$B$8<>"")*($A$1:$A$8<>""))/COUNTIFS($A$1:$A$8,$A$1:$A$8&"",$B$1:$B$8,$B$1:$B$8&""))

The second column A test is redundant; it's always true if "A" is found, and irrelevant if not.

Originally Posted by leelnich

The second column A test is superfluous; it's always true if "A" is true, and irrelevant if not.

Yup! I gave up in disgust (with me) and decided to cover it all. Thanks for commenting though.

Hey Folks. I wanted to share something interesting I noticed about the Formula.

I do not want to reveal too many details about my spreadsheet, but what are your thoughts on the following scenario. In my spreadsheet, Column A is the 50 states and let's assume that Column B represents the names of different fast food restaurants. Let's assume that there is one Fast Food Restaurant, call it BurgerVille that is pervasive across the entire document and across multiple states. BurgerVille represents almost 15% of the entries in Column B. If I run the SUMPRODUCT for a state that does not have a BurgerVille, the formula works perfectly. However, let's assume that a given state, say CA, has 12 Burgervilles and 40 other restaurants. When I run the SUMPRODUCT formula, the answer comes out as 40.325035.

Basically, I think Google Sheets is somehow confused about BurgerVille being in different states, and it's essentially dividing this entry into small chunks. It's as if saying that .325035 of the BurgerVille entries are in CA? Fortunately, this is an easy fix. I just wrapped the SUMPRODUCT with a ROUNDDOWN.

But I wanted to pass along the bug and hear your thoughts. Thanks again as always folks!

Originally Posted by FlameRetired

Yup! I gave up in disgust (with me) and decided to cover it all. Thanks for commenting though.

Still, nice catch on the COUNTIFS($A$1:$A$8,$A$1:$A$8&"",$B$1:$B$8,$B$1:$B$8&"")). I knew there was an obvious fix.

Thanks Lee.

Originally Posted by SlikRick

Basically, I think Google Sheets is somehow confused about BurgerVille being in different states, and it's essentially dividing this entry into small chunks. It's as if saying that .325035 of the BurgerVille entries are in CA? Fortunately, this is an easy fix. I just wrapped the SUMPRODUCT with a ROUNDDOWN.

But I wanted to pass along the bug and hear your thoughts. Thanks again as always folks!

I am not that familiar with Google sheets. Though the first thing I would check would be the data. Are all spellings consistent? Are there leading/trailing spaces? I don't know what the limitations of Google functions are so could Case sensitivity be an issue? Are all "numbers" numeric or are some text "numbers"?

Another thought would be the data source. Often when the source is the internet the non breaking space (character "160") is ubiquitous. They are difficult to detect visually and a pain to search for. SUBSTITUTE is your friend there.

Yet the decimal returns are still odd.