CODEWITHSUNDEEP
excelexcel-formulacountifsumifs
tommyboy
tommyboy

Reputation: 11

Excel - COUNTIF and SUMIF across multiple rows

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.

Upvotes: 1

Views: 1187

Answers (1)

barry houdini
barry houdini

Reputation: 46341

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))

Upvotes: 2

Related Questions