# The SUMIFS Google Sheets Function: Everything You Need To Know

Feb 22, 2023

Do you want to sum values in your Google Sheets based on preconceived conditions?

The SUMIFS Google Sheets function has got you covered. This function allows you to add numbers depending on multiple criteria.

• The difference between the SUMIF and SUMIFS functions in Google Sheets.

• SUM values with multiple criteria.

• Other uses of the SUMIFS Google Sheets Function.

• Sum Values With Multiple Criteria in Separate Columns

• Final Thoughts on the SUMIFS Google Sheets Function

• Related Articles

## The Difference Between SUMIF and SUMIFS Functions in Google Sheets.

• SUMIF Formula =SUMIF(criterion_range,"criterion",sum_range)

• SUMIFS Formula =SUMIFS(sum_range,criteria_range1,"criterion1",criteria_range2,"criterion2",...)

## SUM Values With Multiple Criteria.

Let's define our arguments:

• In Column A, typing in "Strawberries" is the first criterion. Now we will use rows 7 to 15 as our criteria_range1. The cells in this range are A7:A17. Our criterion 1 is "Strawberries."

• The word "Postpone" should be in column C. It means our criteria_range2 is from C7 to C15, and criterion2 is "Postpone."

• The numbers we want to add up are in column B. So our sum_range is from B7 to B15.

When we put all the arguments together, we get a simple formula:

=SUMIFS(B7:B15, A7:A17, "strawberries", C7:C15,"postpone")

For more flexibility, you can enter both pieces of information in different cells. For example, you could enter the information in cells B1 and B2. Then you can refer to those cells when you need that information.

=SUMIFS(B7:B15, A7:A15, B1, C7:C15, B2)

The steps below show the SUMIFS formula in action.

1. Select your criterion1 and criterion2.

1. Put the SUMIFS formula of the selected criteria column ranges and criteria in cell b3.

## Other uses of the SUMIFS Google Sheets Function.

Sometimes, your conditions depend on what other functions say. In this case, put those functions inside the SUMIFS formula.

For example, let's sum "strawberries" that are "transported." This means that the transport date is today or earlier. We concatenate the "<=" operator with the TODAY() function:

=SUMIFS(B7:B15,A7:A15, B2,C7:C15, "<="&TODAY())

1. Input your selected date and the fruit you want to get its sum.

1. After completing the SUMIFS formula, press enter.

1. These are the sum of the strawberries transported using the following formula.

### SUMIFS With Blank Cells and Non-Blank Cells.

Use one of the following criteria depending on whether another column is a blank cell or a non-blank cell:

• "=" is used to sum blank cells. This means that there is nothing in the cell.

• "<>" To sum non-blank cells, including zero-length strings.

• "To sum up, blank cells include zero-length strings.

If some dates are missing in the Transported Date column, you can still sum the "strawberries" by only adding the ones with a date listed. You can do this by using this formula:

=SUMIFS(B7:B15,A7:A15,"strawberries" ,C7:C15, "<>")

## Sum Values With Multiple Criteria in Separate Columns.

The example below shows how to add numbers when the following logic determines several conditions.

• All the conditions in each set must be proper. (AND logic)

• A cell is added up if any set of conditions is actual. (OR logic)

To illustrate better, here is an example.

In our sales data set, let's say you want to add up the numbers in column B if column A has either "Strawberries" OR "Durians," AND the date in column C is "2-Mar-2018".

The most obvious way to accomplish this is to make two SUMIFS formulas. One formula will sum "strawberries," and the other will sum "durians."

Then, you can add up the results of both formulas to get the total amount of the selected multiple columns.

=SUMIFS(B7:B15, A7:A15, "strawberries", C7:C15, "2-Mar-2018") + SUMIFS(B7:B15, A7:A15, "durians", C7:C15, "2-Mar-2018")

## Final Thoughts on the SUMIFS Google Sheets Function.

Now you have the skills to use the SUMIFS function to make your spreadsheet way more organized.

### What is causing SUMIFS to malfunction?

The SUMIF function may not work because the data isn't formatted evenly.

### Is the SUMIFS function the best way to check all the information in a range?

LOOKUP and INDEX-MATCH (type 1) are the fastest ways to find information.

### What function is comparable to the SUMIFS feature in Google Sheets?

The SUMPRODUCT option is similar to the SUMIFS formula because you can add values based on multiple conditions. It works well when there is only one possible match.

## Related Articles:

Google Sheets Countif: Everything You Need To Know

How to Sum a Column in Google Sheets:4 Easy Methods

Excel Countif Function: Simple Guide For Beginners

#### Want to Make Excel Work for You?ย Try out 5 Amazing Excel Templates & 5 Unique Lessons

We hate SPAM. We will never sell your information, for any reason.