SUMIFS with multiple criteria in Google Sheets

Learning to use Sumifs with multiple criteria is key for any Google Sheet user looking to analyze data. It enables you to sum data only if several conditions are met, thus allowing you to segregate data by categories.

In this article we will see how this function works and look at advanced uses. We will also delve into how it can be complemented with Sheetgo features to bring your spreadsheet game to the next level.

Ready to streamline your data?

Understanding SUMIFS function in Google Sheets

This function is designed to add values in a range based on one or multiple criteria. For values to be included in the sum, all conditions must be met.

Unlike SUMIF, SUMIFS can accommodate numerous conditions from different columns, making it better for complex data analysis tasks.

This is the basic syntax of the SUMIFS Google Sheets function:

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

  • sum_range: The range of cells to sum.
  • criteria_range1: The range to test against the first criterion.
  • criterion1: The condition that must be met in criteria_range1 for a cell to be included in the sum.
  • [criteria_range2, criterion2, …]: Additional criteria ranges and their corresponding criteria.

Consider a simple scenario where you want to add values within a column that are between 10 and 20. All the values are in column A, and both criteria also point to this column. This is what the formula would look like:

=SUMIFS(A:A, A:A, “>10”, A:A, “<20”)

Learning how to use SUMIFS is indispensable if you are looking to use spreadsheets for complex analysis.

Advanced uses of SUMIFS

Now we will look at a few advanced uses of SUMIFS, across the following examples we will be centered around the data shown below.

sumifs with multiple criteria 1

Incorporating multiple criteria across different columns

You want to sum up sales for the “Toys & Games” category,  made by “Dave Brown” and paid with a “Debit Card.”

This is what the formula to achieve this would look like:

=SUMIFS(C:C, B:B, “Toys & Games”, E:E, “Dave Brown”, F:F, “Debit Card”)

Just like this you could keep adding criteria to refine the sum that is being carried out. Building on this past example, let’s say now you only want to add sales of items that cost over $10.

You would have to adapt the formula like this:

=SUMIFS(C:C, B:B,“Toys & Games”, E:E, “Dave Brown”, F:F, “Debit Card”, C:C, “>10”)

Sumifs not equal to text

You could also exclude a particular item from the sum by adding a criterion with the not equal to operator (“<>”):

=SUMIFS(C:C, B:B, “Toys & Games”, E:E, A:A, “<>Blox City Space Rocket”)

Sumifs not blank

A slight modification of the last example can help you rule out items with missing data values. For this use case, they could have blank product names.

This is how you exclude these items from the sum:

=SUMIFS(C:C, B:B, “Toys & Games”, E:E, A:A, “<>”)

Sumifs greater than date

If you want to sum values based on a condition involving a date things get a little more complicated. This is how you would add only sales made starting in 2023.

=SUMIFS(C:C, B:B, “Toys & Games”, A:A, “<>”, D:D, “>”&DATE(2023,1,1))

Here you have to use two new resources:

  • DATE() – this function helps you translate a date value into spreadsheet format. Otherwise you have to include dates expressed as whole numbers, that means “01/01/2023” would have been “44927”.
  • The “&” concatenation operator – This allows you to join two values, which otherwise would have been impossible to place side by side.

Utilizing wildcard characters for partial matches

Now you want to analyze sales for a product and you only remember that its name has the word “Blox”. This is a problem, you never remember the full name. But don’t worry, all you have to do is include a criterion with partial text.

To accomplish this, you have to use the wildcard character (*) in your criterion:

=SUMIFS(C:C, A:A, “*Blox*”)

This formula doesn’t make a good use of the function because it only includes one criteria.

Let’s include parts of the previous formula to make the most of SUMIFS:

=SUMIFS(C:C, A:A, “*Blox*”E:E, “Dave Brown”, F:F, “Debit Card”)

These examples illustrate the versatility of the SUMIFS function in Google Sheets, enabling users to perform comprehensive data analysis with multiple conditions and partial matches.

Elevating SUMIFS with Sheetgo

Sheetgo offers features that complement the SUMIFS function in Google Sheets. Adding a robust solution for data analysis, management, and collaboration.

Connect, merge, filter or split your spreadsheets

Connections to simplify data collection

Sheetgo elevates the SUMIFS function by automating the collection and aggregation of data from multiple Google Sheets. Set up connections from different sources and gather information in one spreadsheet before using SUMIFS to analyze it.

Sheetgo connections allow users to seamlessly integrate data from various sources without the manual effort of compiling data sets.

For instance, if sales data is spread across multiple sheets for different regions, Sheetgo can automatically consolidate this data into a single sheet, ready for analysis with SUMIFS.

Forms for easy collaboration

Sheetgo enhances the SUMIFS function’s utility in Google Sheets by integrating forms for easy collaboration. This feature simplifies data entry and collection, allowing users to submit information through custom forms that automatically populate a centralized spreadsheet.

For example, a sales team can use Sheetgo forms to input daily sales data, customer feedback, or inventory updates. These submissions are then automatically aggregated into a master sheet, ready for analysis.

By leveraging forms for data collection, Sheetgo fosters a collaborative environment where information is seamlessly shared and updated.

Workflows to automate business processes

Sheetgo’s automated workflows are a cornerstone for enhancing operational efficiency within organizations. These workflows streamline business processes by automating the transfer and synchronization of data across various Google Sheets. 

For example, in a project management scenario, updates to project timelines or task completions in individual project sheets can automatically update a master project dashboard, providing managers with an up-to-date overview of all projects in real-time.

Furthermore, Sheetgo’s workflows can be customized to trigger specific actions based on data changes, such as sending alerts or emails when certain thresholds are met or tasks are due. This level of automation extends beyond simple data consistency, actively facilitating task management and accountability within teams.

Conclusion and further resources

Now you know the basics of how the SUMIFS function operates and some more advanced applications. Additionally, you discovered how integrating Sheetgo’s features can significantly enhance spreadsheet capabilities.

By combining these techniques, users can unlock the full potential of Google Sheets, leading to more informed decision-making and streamlined workflows.

Want to introduce AI to your work with Google Sheets? Check out 15 awesome Chat GPT prompts for spreadsheet users.

Ready to streamline your spreadsheet data?

You may also like…