Google Sheets SUMIF can handle conditions in multiple columns! Cool, right? See that awesome Sumif multiple columns criteria usage tips.

Sumifs is there! Then why should one go for using Sumif to sum a column but conditions in multiple columns?

I must tell you the benefit. Otherwise, you will end up just learning a cool tip that won’t come in use in real-life.

In my point of view, the one and only benefit or the advantage of SUMIF over SUMIFS is its ability to expand results. The Sumif multiple columns criteria use is relevant at this point.

Google Sheets Sumif with Multiple Columns Criteria

The Sumif function with criterion in multiple columns is possible because SUMIF function in Google Sheets allows the use of Ampersand in range and sum_range. I have mentioned this in my Excel vs. Sheets comparison earlier – How Sumif Function Differed in Excel and Google Sheets.

Sumif with Conditions in Two Columns in Google Sheets

Example:

How to sum “Available Qty.” of the Product “Melon” and the Seller is “Seller 2”?

In this, the Sumif conditions are in two columns, i.e. in the range A2:A6 and B2:B6. But Sumif won’t allow in Sheets to use two ranges as arguments.

As mentioned above, the Sumif multiple columns criteria usage is possible with the help of Ampersand. So combine two ranges using the Ampersand and similarly the criterion. Here is that formula.

Generic Formula:

ArrayFormula(sumif(range 1 & range 2,criterion1 & criterion 2,sum_range))

The Sumif Formula with Criteria in Multiple Columns:

=ArrayFormula(sumif(A2:A6&B2:B6,"Melon"&"Seller 2",C2:C6))

Note: When you use the Ampersand sign to join conditions, don’t forget to use the ArrayFormula as above.

Here comes one important question. In the above example both the conditions are text. What about other conditions like;

Number criteria in one column and text criteria in another column.

In this scenario, join the criteria as per this example. Don’t use the double quotes around the number criteria.

"Melon"&40

Number criteria in both the columns:

Follow this example formula. That means avoid double quotes around both the criteria.

50&40

Note: Both the above criteria usage examples are not based on our original sample data.

So you will now agree that Sumif multiple columns criteria formula works in Google Sheets. Now let me draw your attention to the benefit of this usage.

The Advantage of Using Sumif with Multiple Columns Criteria

When you use multiple criteria columns in SUMIF in Google Sheets, there is one clear advantage. It’s none other than the expanding result.

Sumif workaround can not only take conditions from multiple columns, but also from multiple rows. One single SUMIF formula can do all these.

SUMIF Conditions in Rows and Columns in Google Sheets

Expanding SUMIF Formula in G2:

=ArrayFormula(sumif(A2:A6&B2:B6,E2:E3&F2:F3,C2:C6))

Conclusion

Hope you have enjoyed this SUMIF tips and tricks in Google Sheets.

In the above examples, I have only used conditions in two columns. But you are allowed to add more columns.

There is one drawback of using the Ampersand to join conditions in two columns. It won’t work in cases like one column contains the start date and the other contains the end date. Here combining the dates won’t make any sense, right?

Definitely, I’ll come up with a solution to this dilemma and update you here.