SUMIFS Multiple Criteria in a Single Column

This post will demonstrate how to sum values based on multiple criteria.

But there’s a catch.

The multiple criteria are from the same column.

What if we have more than two criteria?  How can we do this dynamically and easily?

Let’s find out.

We have a data set with Divisions, Regions, and Profit.

How can we get the sum of the Profit for the Utility Division (cell G2) from both the Asia and Europe Regions (cells G3 & H3)?

Notice that we have but a single column of Regions where both Asia and Europe are listed.

Using the SUMIFS Function

If you have seen previous posts on the SUMIFS function, you know that creating a formula that will sum the profits if the Division is Europe and the Region is Asia would be easy.

  • Profit (column D)
  • Division (column A)
  • Europe (cell G2)
  • Region (column B)
  • Asia (cell G3)
=SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3)

Suppose we extend the argument for “which Region” from a single cell (G3) to multiple cells (G3:H3)?  This way, we can include both Asia and Europe.

=SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3)

We are presented with two results: one for “Utility – Asia” and one for “Utility – Europe”.

The reason for the two results is because we are using the new Dynamic Arrays engine in Microsoft 365 (formerly known as Office 365).

This engine can return multiple results from a single formula.

If you are not running Microsoft 365, you will receive an error in the cell.  This is because legacy Excel can’t show two results in a single cell.

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS

Arriving at a Single Result

Whether you are running Microsoft 365 Excel or legacy Excel, our objective of a single, combined result is achieved the same way.

SUM Function to the Rescue

Our solution is to wrap (i.e., nest) the SUMIFS function within a SUM function.

=SUM(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3) )

If you are running the legacy version of Excel, you will need to substitute the ENTER key with a CTRL-Shift-Enter key combination when you finalize the formula.  This will place curly braces around your formula and commit it as an array formula.

“But I don’t like pressing CTRL-Shift-Enter.”

If you are not a fan of using the CTRL-Shift-Enter key combination to create “old-school” array formulas, you can use the SUMPRODUCT function instead of the SUM function when nesting the SUMIFS function.

=SUMPRODUCT(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3) )

Using the SUMPRODUCT function is likely to be the safest approach of the two because it works in all versions of Excel.  This is useful if you are sending this file to non-Microsoft 365 users.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Extending the Region Criteria

If we were to add Australia to the list of regions, we need to adjust our range to include the new Region.

Origin version

=SUMPRODUCT(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:H3) )

Updated version

=SUMPRODUCT(SUMIFS(D2:D28, A2:A28, G2, B2:B28, G3:I3) )

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials