Search code examples
performancegoogle-sheetsgoogle-query-language

SUMIFS function in Google Spreadsheet

I'm trying to have a similar function to SUMIFS (like SUMIF but with more than a single criterion) in a Google Spreadsheet. MS-Excel has this function built-in (http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010342933.aspx?CTT=1).

I've tried to use ArrayFormula (http://support.google.com/docs/bin/answer.py?hl=en&answer=71291), similar to the SUMIF:

=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))

By Adding AND:

=ARRAYFORMULA(SUM(IF(AND(A1:A10>5,B1:B10=1), C1:C10, 0)))

But the AND function didn't pick up the ArrayFormula instruction and returned FALSE all the times.

The only solution I could find was to use QUERY which seems a bit slow and complex:

=SUM(QUERY(A1:C10,"Select C where A>5 AND B=1"))

My Target is to fill up a table (similar to a Pivot Table) with many values to calculate:

=SUM(QUERY(DataRange,Concatenate( "Select C where A=",$A2," AND B=",B$1)))

Did anyone manage to do it in a simpler and faster way?

Solution

  • I found a faster function to fill up the "pivot table":

    =ARRAYFORMULA(SUM(((Sample!$A:$A)=$A2) * ((Sample!$B:$B)=B$1) * (Sample!$C:$C) ))

    It seems to run much faster without the heavier String and Query functions.