Thursday, December 22, 2011

Google Spreadsheet tip: how to SUMIFS


SUMIFS is probably the most useful feature, Microsoft rolled out in Excel 2007. It lets you SUM a range, using multiple criteria. For instance, in the situation below, if we wanted to find out how much John made in 2010 Q3, you could use this simple formula.



Now how do you do this in google spreadsheet? Unfortunately, it only has the older SUMIF formula, which lets you select a single criteria. Let's see how to make it work.





SUM can be chained with the FILTER formula, to achieve the same results. The syntax is very simple.
SUM (Filter ([Sum Range], [All the criteria Ranges separated with a ; or a , ]))
e.g. =sum(filter(D2:D11,C2:C11= "John";B2:B11="Q3";A2:A11=2010))        

Sample example sheet

Pretty easy right!... Enjoy!

9 comments:

SpreadsheetPro.net said...

Nice, you can also make COUNTIFS and AVERAGEIFS using this method. I wrote a tutorial myself on it, let me know what you think!

COUNTIFS and AVERAGEIFS in Google Spreadsheets

Graemac said...

I am getting a circular depenency detected error.

Any ideas?

Anoj said...

Graemac, it should work. If you can share your spreadsheet I can take a quick look and let you know. Thanks!

Dustin said...

Super Helpful! Thanks!

Sainte-Anne Justice GM said...

Thank you so much for this article. Been looking everywhere for this !

Akil Narayana said...

Perfect..This is what exactly I was looking for. Helped precisely.

Amit said...

Good... This helped me. Thanks

moimart said...

How would you input a ">0" criteria? if i leave it with the commas it would read it as a text, right? if i use A2:A11=>0 it's an error too

Great tip, thanks!

Anoj said...

@moimart, it works the same way.
For instance in my example if I want to count rows in 2011 with more than 100k earned, you can use >1000 directly since D contains numerical values

'=count(filter(D2:D11,D2:D11>1000,A2:A11=2011))