CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
user16676189
user16676189

Reputation: 11

ArrayFormula for Google Sheets using Sum and IF

So I have these formulas here

=if(J3="","",sum(J3,M3:N3))

=if(A1="","",index('SHEET1?'!F:F,match(A1,'SHEET1?'!E:E,0)))

I have been trying to find a way to get this as an array but I can't seem to find the solution that works best with this specific format. The problem is that if I do the array formula as is, it shows the sum of the whole range instead of giving the sum per row.

Any help is appreciated. Thank you!

Upvotes: 1

Views: 969

Answers (1)

idfurw
idfurw

Reputation: 5852

SUM is not supported by ARRAYFORMULA, use + or MMULT

=ARRAYFORMULA(if(J3:J="",,J3:J+M3:M+N3:N)))

Alternative:

=ARRAYFORMULA(if(J3:J="",,J3:J+MMULT(N(M3:N),N(TRANSPOSE(COLUMN(M3:N)^0)))))

INDEX is not fully supported by ARRAYFORMULA, use VLOOKUP

=ARRAYFORMULA(IF(A1:A="",,VLOOKUP(A1:A,E:F,2,FALSE)))

Upvotes: 1

Related Questions