logo
logo
Sign in

How to Sum up SUMIFS Function in Google Sheets with Examples?

avatar
Coefficient

This guide will tell you how to use the SUMIFS Function in Google sheets with multiple criteria. In addition, you will get a real-life SUMIFS formula example with AND criteria. 

The main purpose of the SUMIFS function is that you can simplify the workload with a specified criterion. When you use the SUMIFS in Google sheets, you should remember three things. 

  1. The SUMIFS function will meet all the cell conditions, and all of the specified criteria are true for the cell. 
  2. The sum range and all condition ranges should be equally sized. i.e., should have the same number of rows and columns
  3. Depending on the nature of your conditions, you can build your appropriate criteria: 
  • Text, wild card characters, and dates should in enclosed in the quotation. 
  • Cell references and numbers should not be enclosed in the quotations. 
  • Logic operators like numbers, dates, and text must be enclosed in double quotes. 
  • Logic operators should be used together with cell references, and other functions require the quotes to begin the text string and finish the string off. 

How to Sum with Multiple Criteria (AND LOGIC)?

When it comes to summing up your multiple criteria, you might find a lot of different suggestions in Google group. 

Let's suppose you have a list of items in Columns A, B, and C. Your goal is to get a total for a specific item. 

1. "Apples" should be in Column A. Therefore, our criteria range1 is A6:A14, and our criterion1 is "apples," assuming that the data is in rows 6 to 14.

2. "Delivered" should be in Column C. In other words, criterion2 is "delivered," and our criteria range2 is C6:C14.

3. Because the summation numbers are in column B, our sum range is B6:B1.

SUMIFS with Logic Operators 

Use the comparison operators to express the criterion when your conditions are dates or numbers:

• bigger than (>), not equal to (>), equal to (= or omitted), less than (), and not equal to (>)

• Greater than (>=) or equal to

• lower or equal to (=)

How this formula works

Here are the things you do, starting from the inside out:

• Verify whether something is "apple" or "orange":

• Contrast the desired date with a list of dates: C6:C14=B2

• To force the arrays of TRUE and FALSE to 1 and 0, respectively, use the double unary operator (—).

• Provide the range to the sum: B6:B1

This is how you use SUMIFS in Google Sheets to sum cells with multiple conditions. To have a deeply look at the formulas, read out the above formulas. An automated platform makes your life easier, and by using the sum of values, you focus on to the other tasks. A SUMIF function in Google sheets is quite helpful in a variety of situations and provides you with an accurate result. 

collect
0
avatar
Coefficient
guide
Zupyak is the world’s largest content marketing community, with over 400 000 members and 3 million articles. Explore and get your content discovered.
Read more