Table of Contents
With my business, I had an issue on Google Sheets where I wanted to add two numbers automatically. However, the addition operation would only happen if 2 different criteria (2 different conditions) were met in 2 different cells.
Now I already know how to add two numbers based on a single condition from another cell, but this one’s going to be a bit trickier.
Okay, here are the details. I run a side business and I have a tax agent who helps me with my taxes. In my Google Sheets, I have 2 different sources of income and 2 different sources of expenses. I’ve created identical categories in each source like insurance, repairs, and warranty.
Thinking back now, I probably could’ve placed the two income sources in 2 different sheets, but it’s a lot easier to combine the two (since they are both very similar).
So I researched how to do basic addition according to a couple of criterias within the same sheet.
Let’s get started.
How to add up numbers based on 2 conditions
- Open up Google Sheets.
- In the cell you wish to display the sum, type =SUMIFS().
- Within the parentheses, start typing in the number’s cell address (ie. A1) followed by a comma.
- Following the number’s cell address, type in the cell address of the first condition (ie. B1) followed by another comma.
- After the condition address, type in the string value you’re looking for surrounded by double quotations (ie. “condition 1”) followed by a comma.
- Now after the condition value, type in the second condition’s cell address (ie. C1) followed by a comma.
- After this condition address, type in the string value of the condition within double quotations
What should the sum based on 2 conditions formula look like?
=SUMIFS(A1,B1,”condition B1″,C2,”condition C1″,…etc)
What’s the difference between =SUMIF and =SUMIFS?
In practice, =SUMIF requires a less criteria than =SUMIFS. However, =SUMIF can only handle 1 condition, while =SUMIFS can handle a multitude of conditions.
Take a look at the 2 formulas that look differently but these result in the exact same thing:
| =SUMIF | =SUMIFS |
|---|---|
| =SUMIF(A2:A10,“>500”) | =SUMIFS(A2:A10,A2:A10,“>500”) |
| First, location of the numbers for the sum | First, location of the numbers for the sum |
| Second, value we’re testing for | Second, location where condition will test |
| Third, value we’re testing for |
As you might be able to see, =SUMIFS requires an extra step.
The first step between both are identical. It’s asking for the location of the numbers that will be added to the sum.
The second step of =SUMIF, goes straight to the condition of those numbers in step 1. It’s asking what number’s in the range of A2 to A10 are greater than 500. If it is, then add it up.
In the case of =SUMIFS, the second criteria asks again because it’s asking for the address of the cell(s) that will hold the condition. This is something =SUMIF assumes is one in the same, however, it isn’t always the same as the numbers you want to add up. The set here is formed between step 2 and 3 where, step 2 is the address for where to look for the condition in step 3.
In layman terms, add up all the numbers in the range from A2 to A10, if A2 to A10 has a value that’s greater than 500.
This example isn’t common because no one would use =SUMIFS if the values being added is the same as the condition. Instead it would make more sense just to use =SUMIF.
If you really wanted to use =SUMIFS correctly, then you would have to do something like this. Add up all the number in the range from A2 to A10, if B2 to B10 has a value that’s greater than 500.
In this case, the formula would look more like this:
=SUMIFS(A2:A10,B2:B10,“>500”)
Can you create more than 2 conditions with =SUMIFS?
Yes, you can. Simply add another set to the end of the last condition which means putting in a third condition’s cell address (ie. D1) followed by a comma. Then, a third string value within double quotations (ie. “condition 3”).
