Count unique rows containing text without duplicating

[Eskuppe]

Hi,

I hope one of you Excel legends has a solution to my issue.

We have a report that has a column of data that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customers[/TD]
[/TR]
[TR]
[TD]BOB, DAV, HAR, JOH, PAU, PET, PHI, PRA, RYA[/TD]
[/TR]
[TR]
[TD]BOB, HAR, PAU, PHI, PRA[/TD]
[/TR]
[TR]
[TD]DAV, JOH, PET, PRA, RYA[/TD]
[/TR]
[TR]
[TD]DAV, HAR, JOH[/TD]
[/TR]
[TR]
[TD]BOB, DAV, PET[/TD]
[/TR]
</tbody>[/TABLE]

We count how many times each abbreviation comes up per row. I've been using =COUNTIF(range, "*BOB*") which would return 3.

What we need is to count how many times EITHER PAU, PET, PHI or PRA appear. So in the above example, we would need it to return a value of 4.

We're currently getting the total of all the times each one appears so PAU (2) + PET (3) + PHI (2) + PRA (3) = 8. The fact that I'm using * to search within the cell is making it hard to use SUBPRODUCT or DCOUNTA, or a ridiculous amount of COUNTIFS formulas.

Is there a way we can return the amount of unique rows containing either of the four abbreviations mentioned?

 

[Fazza]

what about a query? (or with same SQL to a pivot table)
untested

SELECT COUNT(*)
FROM YourTable
WHERE FieldName = '*PAU*' OR FieldName = '*PET*' OR FieldName = '*PHI*' OR FieldName = '*PRA*'

Brief description. So if your table has defined name YourTable and field name FieldName and the file is saved,
ALT-D-D-N & follow the wizard. At last step take option to edit in MS Query and change SQL to above. OK & see result
Open door icon to exit MS Query & make a query (or pivot) table in a worksheet

cheers

 

[Aladin Akyurek]

Also, if this is what you need, control+shift+enter, not just enter:

Using MMULT...

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(","&$B$1:$B$4&","),","&SUBSTITUTE($A$2:$A$6," ","")&","))+0,ROW($B$1:$B$4)^0),1))

Without MMULT...

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(","&TRANSPOSE($B$1:$B$4)&",",","&SUBSTITUTE($A$2:$A$6," ","")&",")),ROW($A$2:$A$6)),ROW($A$2:$A$6)),1))

A1:A6 houses the data and B1:B4 the customers of interest...

[TABLE="class: grid, width: 404"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]PAU[/TD]
[/TR]
[TR]
[TD]BOB, DAV, HAR, JOH, PAU, PET, PHI, PRA, RYA[/TD]
[TD]PET[/TD]
[/TR]
[TR]
[TD]BOB, HAR, PAU, PHI, PRA[/TD]
[TD]PHI[/TD]
[/TR]
[TR]
[TD]DAV, JOH, PET, PRA, RYA[/TD]
[TD]PRA[/TD]
[/TR]
[TR]
[TD]DAV, HAR, JOH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOB, DAV, PET[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eskuppe]

what about a query? (or with same SQL to a pivot table)
untested

SELECT COUNT(*)
FROM YourTable
WHERE FieldName = '*PAU*' OR FieldName = '*PET*' OR FieldName = '*PHI*' OR FieldName = '*PRA*'

Brief description. So if your table has defined name YourTable and field name FieldName and the file is saved,
ALT-D-D-N & follow the wizard. At last step take option to edit in MS Query and change SQL to above. OK & see result
Open door icon to exit MS Query & make a query (or pivot) table in a worksheet

cheers

Thanks for your suggestion and quick response. I'm not very familiar with SQL. I did try it per your instructions and I must be doing something wrong because it's not quite working for me.

Also, if this is what you need, control+shift+enter, not just enter:

Using MMULT...

=SUM(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE(","&$B$1:$B$4&","),","&SUBSTITUTE($A$2:$A$6," ","")&","))+0,ROW($B$1:$B$4)^0),1))

Without MMULT...

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(","&TRANSPOSE($B$1:$B$4)&",",","&SUBSTITUTE($A$2:$A$6," ","")&",")),ROW($A$2:$A$6)),ROW($A$2:$A$6)),1))

A1:A6 houses the data and B1:B4 the customers of interest...

[TABLE="class: grid, width: 404"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]PAU[/TD]
[/TR]
[TR]
[TD]BOB, DAV, HAR, JOH, PAU, PET, PHI, PRA, RYA[/TD]
[TD]PET[/TD]
[/TR]
[TR]
[TD]BOB, HAR, PAU, PHI, PRA[/TD]
[TD]PHI[/TD]
[/TR]
[TR]
[TD]DAV, JOH, PET, PRA, RYA[/TD]
[TD]PRA[/TD]
[/TR]
[TR]
[TD]DAV, HAR, JOH[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BOB, DAV, PET[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This works like an absolute charm! Thank you so much! Are there any advantages to using MMULT or not using it? They are both yielding the same results.

 

[Aladin Akyurek]

[...]

This works like an absolute charm! Thank you so much!

You are welcome.

Are there any advantages to using MMULT or not using it? They are both yielding the same results.

I did not time them, so no idea about which would be faster, although I suspect the FREQUENCY version might score better.