Update Dec 2013: In the new version of Google Spreadsheets SUMIFS, COUNTIFS, and AVERAGEIFS are already built in. If you didn’t activate the new version yet: read on below!

If you’re used to working in Excel, you’re probably using the SUMIFS, COUNTIFS and AVERAGEIFS functions all the time. When switching to Google Spreadsheet I ran into the problem that I couldn’t use those functions as Google didn’t include them. They only offer the single criterion SUMIF and COUNTIF  and don’t support multiple criterion functions. Luckily, they do offer the FILTER function, which we can use to solve this problem. In this post I’ll show you how you can increase your Google Spreadsheet productivity by replicating the behavior of aforementioned Excel functions.

## Explaining the FILTER Function

With the FILTER function you can select a range based on one or multiple criteria (up to 30). `FILTER(Range; criteria1; criteria2; ...; criteria30)` So when you have the following sheet and you want to make a range that selects the price of the Cars that are Blue and that have more than 20.000 mileage you can make the following function: `=FILTER(B:B;C:C="Blue";D:D>20000)`

This will return a range with prices of cars that meet your criteria. Understanding this, it’s easy to replicate the SUMIFS, COUNTIFS, and AVERAGIFS functions.

Using the Filter function we now have the prices of all cars that meet our criteria. To sum those prices we can simply use the SUM function on our FILTER Result: `=SUM(FILTER(B:B;C:C="Blue";D:D>20000))`

And more general: `=SUM(FILTER(Range; criteria1; criteria2; ...; criteria30))`

To get the count of cars that meet our criteria, we can simply use the COUNTA function on our filter result: `=COUNTA(FILTER(B:B;C:C="Blue";D:D>20000))`

And more general: `=COUNTA(FILTER(Range; criteria1; criteria2; ...; criteria30))`

And lastly, to get the average price of the selected cars, we can use the AVERAGE function like this: `=AVERAGE(FILTER(B:B;C:C="Blue";D:D>20000))`
And more general: `=AVERAGE(FILTER(Range; criteria1; criteria2; ...; criteria30))`