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 1:

The simplest way to easily make SumIFS-like functions in my opinion is to combine the FILTER and SUM function.

SUM(FILTER(sourceArray, arrayCondition_1, arrayCondition_2, ..., arrayCondition_30))

For example:

SUM(FILTER(A1:A10;A1:A10>5;B1:B10=1)

Explanation: the FILTER() filters the rows in A1:A10 where A1:A10 > 5 and B1:B10 = 1. Then SUM() sums the values of those cells.

This approach is very flexible and easily allows for making COUNTIFS() functions for example as well (just use COUNT() instead of SUM()).

Solution 2:

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.

Solution 3:

As of December, 2013, Google Sheets now has a SUMIFS function, as mentioned in this blog post and documented here.

Note that old spreadsheets are not converted to the new version, though you can try copy-pasting the data into a new workbook.

Solution 4:

This guy used the Filter function to chop down the array by the criteria, then the sum function to add it all in the same cell. http://www.youtube.com/watch?v=Q4j3uSqet14 It worked like a charm for me.