Is it possible to include multiple sum columns in the SUMIF function in Google Sheets?
Usually, it’s not possible. But I have a trick to use more than one sum column in the Sumif function. It will work in array form too.
When you try to include multiple sum columns in Sumif in Google Sheets, you will end up with #N/A error or the sum of the first column only.
Let’s go to one example.
How to Include Multiple Sum Columns in Sumif Function in Google Sheets
Here is the trick to include more than one sum column in the Sumif function in Google Sheets. As far as I know, it won’t work in Excel.
Syntax: SUMIF(range, criterion, [sum_range])
Steps:
- Find the number of columns to sum.
- Duplicate the ‘range’ parameter as per the number of sum columns. If there are 2 columns make the duplicate of the range 2 times (Syntax – {range,range,…}).
- Then use multiple sum columns as the sum_range.
Example:
As per our above ‘fruit’ data, the range is A2:A5, and the number of columns to add is two. So we must duplicate the range A2:A5 twice. How?
Here it is.
{A2:A5,A2:A5}
Now see the Sumif formula.
=sumif({A2:A5,A2:A5},”Lemon”,B2:C5)
This would return 900 as the output.
Yes! The above is the smart trick to include two sum columns in the Sumif function in Google Sheets. This will work in Array Formula (multiple criteria) Sumif too.
Example:
=ArrayFormula(sumif({A2:A5,A2:A5},{“Lemon”;”Avocado”},B2:C5))
If there is one more column, i.e., column D, then the formula would be;
=sumif({A2:A5,A2:A5,A2:A5},”Lemon”,B2:D5)
If there are several sum columns in the Sumif Sum_range, duplicating the range won’t be practical. In that case, we can simplify the formula as below.
I am rewriting the just above formula with a dynamic range formula.
=ArrayFormula(sumif(if(len(B2:D5)>=0,A2:A5),”Lemon”,B2:D5))
range: if(len(B2:D5)>=0,A2:A5)
criterion: “Lemon”
Sum_range: B2:D5
There are several smart workarounds/alternatives too. To learn that, let’s use the sample data on the screenshot below (only the first 3 columns).
Workarounds that Involve Sumif
On the image, you can see the output of five formulas in D2:H2 and the formula descriptions in D1:H1.
The five formulas sum the columns B and C if the name of the days of the week in column A is “Sun”. That means the criterion is “Sun”.
If you are a basic Google Sheets user and only want to stick with SUMIF, you can follow the below helper column-based solution. It would be easy for you to understand.
It’s easy to use SUMIF with a helper column to include multiple sum columns.
I am starting with the helper column solution first. Then you can see the other five different formulas. Choose the one that you think best suits you.
Sumif with Helper Column
Example:
=sumif(A2:A13,”Sun”,D2:D13)
I have used column D, which is the helper column, as the sum_range in SUMIF. Column D contains the total of columns B and C.
I know most of you are not in favor of the helper column use. I myself use helper columns very rarely. So here is a workaround using two Sumif formulas.
By Adding Multiple Sumif Formula Results
I don’t think you need any explanation. In the below example, the first SUMIF formula sums column B. The second one sums column C.
Formula:
=sumif(A2:A13,”Sun”,B2:B13)+sumif(A2:A13,”Sun”,C2:C13)
Alternative Formulas to SUMIF with Multiple Sum Columns
Here are the most useful alternative formulas in case you want to use multiple sum columns in Sumif in Google Sheets.
SUM and IF Statement Alternative to Sumif with Multiple Sum Columns
Yes! this time I am using the function SUM and IF logical to replace SUMIF. This is one of the widely used formulas in Excel.
=ArrayFormula( SUM( if(A2:A13=”Sun”,B2:B13+C2:C13) ) )
But in Excel instead of the function ArrayFormula, you should enter the formula using Ctrl+Shift+Enter.
You May Like: Array Formula: How It Differs in Google Sheets and Excel.
There are still even better alternatives to multiple sum column Sumif in Google Sheets. Please read on.
Query Alternative to SUMIF with Multiple Sum Columns in Google Sheets
As I have said many times in the past, Query is one of the best functions in Google Doc Sheets. You can use Query as a substitute for many other functions in Google Sheets.
See how Query replaces SUMIF in this case. It’s one of the elegant solutions.
=sum( query( A1:C,”Select B+C where A=’Sun'” ) )
Filter and Sum Combo – Recommended ✓
This is one of the best and also my recommend option to use as an alternative to Sumif with multiple sum columns in Google Sheets.
Formula:
=sum( filter(B2:C13,A2:A13=”Sun”) )
The above formula uses the Filter function to filter columns B and C (sum_range) based on the criteria, i.e., “Sun”, and then sums the columns.
Now here is the final solution.
Sumproduct – Recommended ✓
I know many Google Sheets users are out there who frequently use SUMPRODUCT to replace SUMIF/SUMIFS.
The reason, SUMPRODUCT can make your formulas shorter, smarter, and easy to read. See this one!
Formula:
=SUMPRODUCT(B2:C*(A2:A=”Sun”))
I have provided a couple of formula options above for you to use when you want to use multiple sum columns in Sumif in Google Sheets.
Which one do you prefer? Like to hear your views in the comments below. Enjoy!
Related Reading:
- How to Use SUMIFS to Sum Multiple Columns in Google Sheets
- How to Use the Sumif Function Horizontally in Google Sheets
- Sum of Matrix Rows or Columns Using Sumif in Google Sheets
