This tutorial is all about how to use SUMIF or SUMIFS formula in google sheets.
Personally, I use Excel Sumifs formula all the time but the question is do we have a similar function in google sheets as well.
Let’s find out.
How to use Sumifs in Google sheets
If you already know how to use sumif formula in excel then I think this will be a cakewalk.
If not then no worries, I’ll walk you through.
Let’s learn Sumif (not if’s)
I just opened a blank Google sheets file and generated some dummy data and here is a summary of what I have.
So in my next sheet, I’m trying to find out ‘Sales’ & ‘Revenue’ numbers by a select few countries – here is what I’m looking for (the ‘yellow’ cells).
For this example there is only one criteria i.e. total sales by country or revenue by country hence we can just use ‘SUMIF’ instead of ‘SUMIFS’.
SUMIF Syntax in google sheets
Sumif has three components:
range – this is a criteria range [in our case its Country Code, the entire column in data sheet]
criterion – this is where you define your criteria [in our case its each country code in the results sheet]
sum_range – the data that you want to sum [ Sales & Revenue in our case]
Here is a snapshot after adding sumif formula. The data screenshot is also added below for easy understanding.
I’ll use the same data for SUMIFS as well but this time I’ll add one more criteria i.e. I’d like to see revenue & sales for a select few countries and also for a specific product.
The Google Sheets SUMIFS formula is almost similar but the only difference is you can add multiple criteria’s.
Here is a syntax for SUMIFS.
And here is a screenshot after adding formula with multiple criteria’s.
Data!F:F ==> sum range [entire Revenue column]
Data!C:C ==> criteria range1 [entire Country Code column]
A4 ==> ==> Criteria1 [country code in below summary table]
Data!D:D ==> criteria range2 [entire Product column]
B4 ==> criteria2 [Product in the below table]
Please note, SUMIFS is not just limited to two conditions you can add multiple
<em> </em>according to your requirements. (to be honest, I’m not sure about the maximum number of conditions)