Please advise what is wrong with my formula

[lostboyca]

=COUNTIF(J10:J623,"Pass")-SUMPRODUCT((I10:I623="test_Exit_CWS, test_Exit_HF")*(J10:J623="Pass"))

It's only counting the cell that has test_Exit_CWS. I need it to count test_Exit_HF as well.

Thanks

 

[Haseeb Avarakkan]

Hello, Try this SUMPODUCT

=SUMPRODUCT(--ISNUMBER(MATCH(I10:I623,{"test_Exit_CWS","test_Exit_HF"},0)),--(J10:J623="Pass"))

 

[lostboyca]

Hello, Try this SUMPODUCT

=SUMPRODUCT(--ISNUMBER(MATCH(I10:I623,{"test_Exit_CWS","test_Exit_HF"},0)),--(J10:J623="Pass"))

Looks promising. I do not want the calculation to count anything that has test_Exit_HF or test_Exit_CWS that has Pass. I want it to count other words in the cell that has pass

 

[Haseeb Avarakkan]

Is this do you mean?

1. I10:I623 not contains test_Exit_CWS or test_Exit_HF.
2. I10:I623 is not blank
3. J10:J623 = "Pass"

If so, try

=SUMPRODUCT(--(I10:I623<>""),--ISNA(MATCH(I10:I623,{"test_Exit_CWS","test_Exit_HF"},0)),--(J10:J623="Pass"))

 

[lostboyca]

you are freaking awesome!!! thanks a million and a half