20

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Guy
  • 12,388
  • 3
  • 45
  • 67

4 Answers4

15

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

Javaaaa
  • 3,788
  • 7
  • 43
  • 54
  • I see you wrote a post on it as well that goes into more depth, might be useful to mention: http://spreadsheetpro.net/how-to-make-sumifs-countifs-averagifs-functions-in-google-spreadsheet/ – Javaaaa Jul 21 '13 at 02:58
11

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.

Guy
  • 12,388
  • 3
  • 45
  • 67
  • 2
    another idea, if I got your question right, would be `=ARRAYFORMULA(SUMIF(Sample!A:A&Sample!B:B;A&B;Sample!C:C))` – cregox Sep 02 '12 at 13:28
  • I really like Guy's method. I was using the `SUM(FILTER(sourceArray, arrayCondition_1, ...` method from Javaaaa above but was annoyed that I had to wrap it in an `IFERROR()` to catch `#N/A` cases when `FILTER()` returned zero results. The "pivot table" method doesn't error out with zero results like I wanted. Sweet! – Rob Barreca Oct 12 '16 at 02:39
7

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.

yoyo
  • 8,310
  • 4
  • 56
  • 50
1

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.

Graham
  • 11
  • 1
  • 1
    The method in the video is similar to the QUERY method which I found to be rather slow on a big spreadsheet. Even with 4 rows it took a second to calculate. – Guy Jul 14 '12 at 00:58