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.

Spreadsheets are just the start

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.

Sheetgo for your spreadsheet needs

Managing data in Google Sheets can be streamlined with Sheetgo. Whether you are dealing with large datasets or complex workflows, Sheetgo offers solutions to automate and optimize your processes.

Key Features:

  • Automated Workflow Creation: Create workflows to automate data transfers between sheets.
  • Data Consolidation: Combine data from multiple sheets into one.
  • Scheduled Updates: Set up automatic updates to keep your data current.

Advantages:

  • Efficiency: Save time with automated processes.
  • Accuracy: Reduce errors with reliable data transfers.
  • Scalability: Manage large datasets with ease.

Try Sheetgo today to enhance your Google Sheets experience and take control of your data management.

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.

Ready to streamline your spreadsheet data?

You may also like…