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.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
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
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.
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.
