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:
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
I am getting a circular depenency detected error.
Any ideas?
Graemac, it should work. If you can share your spreadsheet I can take a quick look and let you know. Thanks!
Super Helpful! Thanks!
Thank you so much for this article. Been looking everywhere for this !
Perfect..This is what exactly I was looking for. Helped precisely.
Good... This helped me. Thanks
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!
@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))
Post a Comment