CODEWITHSUNDEEP
google-sheetssumtransposegoogle-sheets-formulaarray-formulas
Omar
Omar

Reputation: 117

How to sumif in arrayformula in different ranges

I want to use sumif in an array formula to count the number in these ranges C:C R:AH, these ranges contains numbers from 1 to 10 and "above 10" which I consider it as 11 but I can't name it 11 in the sheet

I tried this equation but it didn't work =ARRAYFORMULA(IF(ROW(A:A)=1,"Count of errors",IF(LEN(A:A),IF(IFERROR(REGEXEXTRACT(TRANSPOSE(QUERY(TRANSPOSE(IFS(ISNUMBER({C:AH}), "♦",{C:C,R:AH}="Above 10","♦" )),, 999^99)), "♦"))="♦", 1, 0), )))

I want the equation to sum the numbers in the range C:C,R:AH as in row 1 in the sample data the columns contain Above 10, 4 & 2 so I need the equation to display 17 and so on

Sample Data: https://docs.google.com/spreadsheets/d/1SksZv0h82j5oEZBj2AN5anDFr80AYNR5ettSwkpUKys/edit?usp=sharing

Upvotes: 1

Views: 121

Answers (1)

player0
player0

Reputation: 1

all you need is simple MMULT with SUBSTITUTE for "Above 10":

=ARRAYFORMULA({"Count of errors"; IF(LEN(A2:A), 
 MMULT(SUBSTITUTE(C2:AH, "Above 10", 11)*1, TRANSPOSE(COLUMN(C2:AH)^0)), )})

0

for {C2:C, R2:AH} use:

=ARRAYFORMULA({"Count of errors"; IF(LEN(A2:A), 
 MMULT(SUBSTITUTE({C2:C, R2:AH}, "Above 10", 11)*1, TRANSPOSE(COLUMN(Q2:AH)^0)), )})

Upvotes: 1

Related Questions