An Ultimate Guide about SUMIFS Function in Google Sheets

Now the sum of values in the spreadsheet has become a piece of cake with the help of SUMIFS Google sheets that perform the actions on predefined conditions.

It is not the same as the SUMIF function, which only adds values to the sum as per one condition. SUMIFS can implement two or more conditions. But on the other hand, the SUMIFS Google Sheet function only adds a term or value to a sum when all the conditions meet.

In this guide, we will explain how to master SUMIFS in Google Sheets with the help of some great examples and a step-by-step guide.

So, without any further ado, let’s get started!

But first, let us understand the use of the SUMIFS function in Google sheets.

Why Should You Use SUMIFS Function in the Google Sheets?

You might know that, in Google Sheets, the most fundamental way to achieve the sum of cell values is by using an additive formula, i.e., =B1 + B2 + B3 + B4 + B5.

But this basic formula isn’t considered a good option when a user wants to add longer ranges of cells, such as entire rows or columns. For this, the SUM option is available. But it is only feasible and considered a good option when you want to sum up to a certain range.

SUMIFS is a SUM function with amazing powers. For example, it can help you easily calculate sums across different ranges. Moreover, you can exclude certain values from the sum.

Now, let us move a step ahead and understand its syntax.

Syntax of the SUMIFS Function in Google Sheets

The basic syntax of the SUMIFS function is like: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

Let’s understand the syntax’s arguments in depth:

• Sum_range: It defines the range of cells you want to consider for the sum.
• Criteria_range1: The range of cells you want to consider to test your criteria.
• Ceriteria1: It explains the criteria for the cells as defined in the criteria_range1. The cells that meet the conditions of Criteria_range1 are added to the sum.
• Criteria_range2, criteria2: Additional ranges and criteria are considered within a SUMIFs function. Maximum 127 range/criteria pairs a user can add into a per formula.

Pro Tips for using SUMIFS in Google Sheets

Using SUMIFS in Google Sheets is relatively easy. Consider the following tips to ensure a flawless process.

• SUMIFS only adds cell values that match the predefined conditions. It is almost similar to the logical AND operator in programming languages.
• It would be best if you confined to text, dates, and wildcard characters in quotation marks.
• You must also ensure that the criteria_ranges will not exceed the sum_range, or the result will be in a #VALUE error.
• You must include logical operators in double quotes between text, numbers, and dates.

These are some of the pro tips that will help to keep the most common errors away. So now, let us step ahead and understand the SUMIFS location operators.

How to Use SUMIFS Function with Logical Operators?

Users should consider using a comparison operator when criteria are related to dates or numbers. Here is the guide on how to use comparison operators.

• Greater than (>)
• Less than (<)
• Greater than or equal to (>=)
• Less than or equal to (<=)
• Equal to (= or omitted)
• Not equal to (<>)

A SUMIFS in Google Sheets makes it better to sum values according to the predefined conditions. If you master SUMIFS, you can strengthen your spreadsheet capabilities and turn your formulae more efficiently.