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!
Post a Comment