Excel - COUNTIF and SUMIF across multiple rows

1
votes

I have a sheet with multiple columns of "Yes" / "No". I need to count the number of rows that have >5 "Yes". Obviously determining if an individual row should be included is easy using countif, but I can't figure out how to make the row 'eligible to be counted' and then sum the number of rows that meet my criteria.

1
excelexcel-formulacountifsumifs
Create a helper colum that will count if Yes... Btw, if you post your data we can help you. Or at least an image of your spreadsheet. - dot.Py
Use a helper column of countifs() for each row. Then at the bottom use another countif that counts all those >5. Or create a UDF with vba that does what you want. - Scott Craner
Oh man. Duh. Thank you! Is there a way to do it without the helper column - just for my excel knowledge. I'm guessing there is a way to do it but couldn't figure it out. - tommyboy
There are ways to do this with a single formula - assuming the columns are adjacent, i.e. in one range - see my answer below - barry houdini

1 Answers

2
votes

Assuming your range is A2:Z100 this array formula will count the number of rows with 5 or more "Yes" entries

=SUM((MMULT((A2:Z100="Yes")+0,TRANSPOSE(COLUMN(A2:Z100)^0))>=5)+0)

confirm with CTRL+SHIFT+ENTER

or you can use this version with FREQUENCY

=SUM(IF(FREQUENCY(IF(A2:Z100="Yes",ROW(A2:Z100)),ROW(A2:Z100))>=5,1))

....which also needs "array entry"

or a third approach with COUNTIF - doesn't need array entry

=SUMPRODUCT(0+(COUNTIF(OFFSET(A2:Z100,ROW(A2:A100)-ROW(A2),0,1),"Yes")>=5))