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.
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))
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))
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))
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.
That’s it – now you know how to make SUMIFS, COUNTIFS, and AVERAGEIFS in Google Spreadsheet. If you want to see the example in action, check out the following Google Spreadsheet. Let me know in the comments when this tutorial helped you or feel free to ask any questions you might still have!
