How to SUMIFS With Multiple Criteria In the SAME Column in Excel, by Leila Gharani on YouTube. 4 min 25 s.
- Video length
- 4 min 25 s
- Time to do it
- 10 minutes
- Difficulty
- Medium
- Steps
- 8
What you need
Steps
Open your data table and note the column holding the criteria, such as Region, and the column holding the numbers to sum, such as Profit.
Start with a normal SUMIFS to check your ranges: =SUMIFS(Profit range, Region range, "Asia"). This sums one region only.
To add a second region in the same column, wrap the criteria in an array constant: =SUMIFS(Profit, Region, {"Asia","Europe"}).
In Office 365, press Enter and the formula spills the result. In older Excel, enter it with Ctrl+Shift+Enter as an array formula.
To add more regions, extend the list inside the braces, for example {"Asia","Europe","Australia"}.
If your Excel version rejects the array inside SUMIFS, use =SUMPRODUCT(SUMIFS(Profit, Region, {"Asia","Europe"})) to get a single total.
Combine this with a second criterion in another column, such as a product type, by adding the range and value pair after the first pair.
Check the total against a quick manual filter of the same rows before you use the result in a report.
Tips and common mistakes
Use commas or semicolons inside the braces to match your Excel regional settings, or the formula returns an error.
The braces are typed by hand, not added by pressing Ctrl+Shift+Enter.
Keep criteria ranges the same size as the sum range or SUMIFS returns a #VALUE error.
Wrap the formula in SUMPRODUCT when you need one total instead of a spilled list.
Questions
Can SUMIFS handle OR logic in one column?
Not directly with a single value. You pass a list of values in braces, like {"Asia","Europe"}, so the formula sums any row that matches one of them.
Does this work in Excel 2016 and 2019?
Yes, but you may need to enter the formula with Ctrl+Shift+Enter, or wrap it in SUMPRODUCT, because those versions do not spill arrays the way Office 365 does.
Why do I get a #VALUE error?
The most common cause is mismatched range sizes or the wrong list separator inside the braces. Check both against your regional settings.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
