Please help. I'm struggling to Count Yes'/No's based on matching text.

Copper Contributor

Hi there,

 

I want to count in column K the number of "Pass" in 'Test Pass/Fail' column of the left table. (See image).

 

I want to find the pass count for each individual measure and have them appear in the smaller table on the right.

 

This workbook will eventually be full of lots of different measures and I want to count the amount of "Pass" for every single one of them to then convert into a Pass Rate (%).

 

I've so many formulas and combinations including COUNTIF, INDEX, MATCH, SUMIF and I just cannot figure out how to do this. It seems so simple but I'm struggling so much.

 

Any help would be really appreciated!

5 Replies
Hello,
In cell K4, execute the formula:
=COUNTIFS(Table1[Measure],J4,Table1[Test Pass/Fail],"Pass")
Copy down the formula

Table1 in this case, is your Table name because I can see that you formatted your data in Excel Table...

In case you provided descriptive Table name i.e Data... You will have to replace all Table1 with Data..

=COUNIFS(Data[Measure],JA,Data[Test Passs/Fail],"Pass")

Hi @Abiola1 

 

Thanks for your suggestion. 

 

I tried what you suggested and it seemed to work until I copied it down. 

 

It's counting a Fail as Pass In Cell K5. I also tried the formula to count Fails in an additional column on the table on the right and that is having the same problem.

 

Any suggestions on what's going on?

 

Cheers,

Based on the attached picture, I can see that you have space in the criteria_range2 and criteria2.

Kindly remove the two spaces... It should work fine
This is what your formula will look like in cell K5.

=COUNTIFS(Table3[Measure],J5,Table3[Test Passs/Fail],"Pass").

Click enter and copy down the formula... It should be fine

@ryanjdavis95 Replicated your example in the attached workbook (different table names though), which I believe to holds the solution you want.