Making SUMIFS, COUNTIFS, & AVERAGEIFS functions in Google Spreadsheet

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)

Using the Filter function to select a range based on multiple criteria

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.

The resulting range of the Filter function’s criteria

SUMIFS in Google Spreadsheet

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

SUMIFS in Google Spreadsheet

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

COUNTIFS in Google Spreadsheet

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

COUNTIFS in Google Spreadsheet

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

AVERAGEIFS in Google Spreadsheet

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

AVERAGEIFS in Google Spreadsheet

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

Note: Google Spreadsheet also doesn’t have the single criterion AVERAGEIF function. You can apply the same method of using the FILTER with only one criterion to simulate the AVERAGEIF function yourself.