How to SUMIF in Google Sheets

The SUMIF function in Google Sheets allows you to sum values in a range based on specific criteria. Whether you’re dealing with financial data, inventory lists, or any other dataset, it can perform calculations efficiently.

This guide will walk you through the syntax of the SUMIF function, provide practical use cases, and introduce the SUMIFS function to handle multiple criteria.

Syntax

=SUMIF(range, criterion, [sum_range])

range: The range of cells that you want to apply the criteria to.
criterion: The condition that must be met for a cell to be included in the sum.
sum_range (Optional):The range of cells to sum. If omitted, the function sums the cells in the range argument.

The example below sums the values in B1:B10 where the corresponding cells in A1:A10 are greater than 5.

=SUMIF(A1:A10, “>5”, B1:B10)

Use cases of SUMIF in Google Sheets

SUMIF greater than 0

The SUMIF function can sum values that are greater than zero.

This is particularly useful for financial data, where you may want to exclude negative balances or zero values from your totals.

The example below the function sums all values in A1:A10 that are greater than 0.

=SUMIF(A1:A10, “>0”)

SUMIF contains partial text

The SUMIF function can also be used to sum values based on text criteria. You can even include wildcards to consider partial matches.

This is handy when working with datasets that include text entries, such as product names or categories, and you want to sum related numeric values.

The example below sums the values in A1:A10 where the cells contain the substring “text”.

=SUMIF(A1:A10, “*text*”)

SUMIF does not equal

Sometimes, you may need to exclude specific values from your sum.

The SUMIF function can sum values that do not equal a criterion, which is great to filter out unwanted data.

The example below sums the values in A1:A10 where the cells do not equal 5.

=SUMIF(A1:A10, “<>5”)

Google Sheets SUMIF multiple criteria

Unfortunately, there is no straightforward way to include several conditions using the SUMIF function alone.
For scenarios requiring multiple criteria, Google Sheets provides the SUMIFS function, which is specifically designed to handle multiple conditions efficiently.

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

The example below sums the values in B1:B10 where the corresponding cells in A1:A10 are greater than 0 and the cells in C1:C10 are less than 5.

=SUMIFS(B1:B10, A1:A10, “>0”, C1:C10, “<5”)

For more details on using SUMIFS for multiple criteria, check out our SUMIFS article.

Conclusion

Mastering the SUMIF function in Google Sheets can significantly enhance your data analysis capabilities.

Additionally, Sheetgo can further streamline your workflow by automating data transfers and consolidations, making your data management process more efficient and accurate.