We have a data set with DivisionsRegions, 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.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.