I am trying to automatically count samples that pass or fail based on multiple criteria. Countifs works beautifully for the pass condition where all criteria must be passed, but I’m struggling with the fail condition. A fail is defined when any single criteria fails.
I’m trying to make a summary table of the data that looks like this:
With data that looks like this:
To count passing samples, I use: =COUNTIFS(B7:B11,"Pass",C7:C11,"Pass")
What formula should I use to count fails? =COUNTIFS(B7:B11,"Fail",C7:C11,"Fail") returns “1” for the sample that failed both criteria.
3
Count Rows Where All Cells Meet a Condition
Step By Step
=LET(data,B2:C6,yes,"Pass",
yd,IF(data=yes,1,),
rc,ROWS(yd),
cc,COLUMNS(yd),
y,BYROW(yd,LAMBDA(r,SUM(r))),
o,IF(y=cc,1,),
p,SUM(o),
r,VSTACK(p,rc-p,rc),
r)
-
Replace the last
rwith any other variable to see what it holds. -
The
rinsideBYROWis a different variable. -
If you have access to the ETA lambdas, you could use:
y,BYROW(yd,SUM),
Short
=LET(data,B2:C6,yes,"Pass",
y,IF(data=yes,1,),
r,ROWS(y),
p,SUM(IF(BYROW(y,SUM)=COLUMNS(y),1,)),
VSTACK(p,r-p,r))
- IMO you have oversimplified your actual case. I’m looking forward to the question about it.