ISNUMBER and FIND in finding case-sensitive values based on the criteria

[JCEurovision]

Good evening. I need some help.

Using ISNUMBER, FIND, and any criteria-based formula (IF, COUNTIF, COUNTIFS, SUMIF, and/or SUMIFS), how can I determine the number of case-sensitive cells that satisfy the given condition/s? As in, how can Excel compute the number of cells that met the given text AND the given condition?

I am using Excel 2021 for this, and I am hoping for any replies regarding this.

Thank you.

[nick.williams]

Something like this, in E2?

=SUM(IF($B$2:$B$51="VALID",1,0)*IF(ISNUMBER(FIND(D2,$A$2:$A$51)),1,0))

[Phuocam]

Like this?

=SUMPRODUCT(EXACT($A$2:$A$51,D2)*EXACT($B$2:$B$51,"VALID"))

[JCEurovision]

Seems very close, but here's the workbook that I've made.

[nick.williams]

What's not working?

[JCEurovision]

It should've totaled 81 occurrences but when I highlighted the cells, there are 114 occurrences.

Row D8 should've been: 3 4 3 8 10 11 9 13 8 7
When I entered the formula: 36 18 9 18 15 15 10 16 9 9

[nick.williams]

Ah I see. The problem arises because you have two entries which are similar; "Wonder" and "Wonderland", so when you search for "Wonder" you get both.

Looking at your source data, it seems the search term is always enclosed in quotation marks, so you could replace:

'Summary of Data'!$D2

with

CHAR(34)&'Summary of Data'!$D2&CHAR(34)

which should solve the problem.

[JCEurovision]

Could you please clarify what you mean? Here's the formula that I attempted with:

=SUM(IF(Raw_Data[STATUS]="VALID",1,0)*IF(ISNUMBER(FIND('Summary of Data'!$D2,Raw_Data[12 points (Heat 1)])),1,0))

[nick.williams]

Change:

=SUM(IF(Raw_Data[STATUS]="VALID",1,0)*IF(ISNUMBER(FIND('Summary of Data'!$D2,Raw_Data[12 points (Heat 1)])),1,0))

To:

=SUM(IF(Raw_Data[STATUS]="VALID",1,0)*IF(ISNUMBER(FIND(CHAR(34)&'Summary of Data'!$D2&CHAR(34),Raw_Data[12 points (Heat 1)])),1,0))

[JCEurovision]

If so, then how would that formula apply to all cells?

[nick.williams]

You could just do a find and replace on the one you had already done, and copy down to the other cells.

A more robust method might be to put this in cell F2:

=SUM(IF(Raw_Data[[STATUS]:[STATUS]]="VALID",1,0)*IF(ISNUMBER(FIND(CHAR(34)&'Summary of Data'!$D2&CHAR(34),Raw_Data[12 points (Heat 1)])),1,0))

Then drag it (using the bottom right corner of the cell, NOT copy and paste) across and down, and the column references should update correctly.