Google Sheets SUMIFS and SUMIF with multiple criteria – formula examples

Google Sheets SUMIFS and SUMIF functions also present in Microsoft Excel for doing conditional sum based on single or multiple criteria only.

Is very helpful in doing conditional sums and that is the objective of out tutorial today.

So, we have the data i.e. first table as shown below:

And our target is to fill the two tables as shown below using SUMIF and SUMIFS.

But, always remember that there is difference between SUMIF and SUMIFS function, because of the number of variables that we can input to them.

And that is what is discussed in coming section.

Difference between SUMIF and SUMIFS:

FunctionExplanation
SUMIFFor conditional sum based on one criteria only
SUMIFSFor conditional sum based on multiple criteria

So, always remember that when one have single criteria then use SUMIF; but when the criteria increase from one use SUMIFS.

SUMIF Explanation:

where,

  • Range: Column or variable to be tested against our criteria or variable.
  • Criteria: Criteria based on which we want to do the conditional sum.
  • [Sum_range]: Column or variable to be summed up.

So, we have just seen the variables that we provide as input to SUMIF function.

But, simply quoting the formula is not enough and so refer the section below for real example of the same.

Refer the image below for the example:

In the image above we have summed up the value of “Quantity A” w.r.t Direction.

Similarly, we can do the same for “Quantity B” as well as for other directions as well.

SUMIFS Explanation:

where,

  • Sum_range: Column or variable to be summed up.
  • Criteria_range1: Column or variable to be tested against our first criteria or variable.
  • Criterion1: Criteria based on which we want to do the conditional sum.
  • Criteria_range2: Column or variable to be tested against our second criteria or variable.
  • Criterion2: Second Criteria based on which we want to do the conditional sum.

Refer the image below for the example:

In the image above we have summed up the value of “Quantity A” w.r.t Direction & Country.

Similarly, we can do the same for “Quantity B” as well as for other cells of “Quantity A” column as well.

For more article like “Google Sheets SUMIFS and SUMIF” functions, please write to us.

Miscellaneous Discussion

Now, because SUMIF and SUMIFS never comes in use directly, so a practical analysis example or video will be of great help.

Hence, follow our youtube channel on GOOGLE SHEETS TUTORIAL and DATA ANALYSIS, so that you can have idea about real case scenario as well.

Other Articles:

Google Support: Link

Leave a Comment