10 *next level* SUMIFS tricks only Excel Pros KNOW, by Chandoo on YouTube. 10 min 5 s.
- Video length
- 10 min 5 s
- Time to do it
- 25 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Lay out your data as a clean table with a header row. Put the numbers you want to add in one column and the labels you will filter on in the columns beside it.
Write a basic SUMIFS formula: =SUMIFS(value_range, criteria_range1, criteria1). The value range holds the numbers to add and each criteria range must be the same size.
Add a second condition by appending another range and criteria pair, for example =SUMIFS(D:D, A:A, "North", B:B, "Q1"). Both conditions must be true for a row to count.
For OR logic, add two SUMIFS results together or wrap them in SUM, so a row counts if it matches either condition.
Use comparison operators inside quotes, like ">100" or "
Total between two dates with two conditions on the same date column: one ">=" start date and one "
Use wildcards in criteria to match patterns. "*west*" matches any text containing west, and "?" matches a single character.
Swap SUMIFS for MAXIFS or MINIFS to find the largest or smallest value that meets your conditions, or use MEDIAN with an array for a median.
Count unique entries by combining COUNTIFS with UNIQUE and COUNTA in Microsoft 365, or use SUMPRODUCT for older versions.
Tips and common mistakes
Make all ranges the same height and width, or SUMIFS returns a #VALUE! error.
Put operators and wildcards inside quotes, and join a cell reference with & when you need a dynamic date or number.
Use whole-column references like A:A only on small sheets, since they slow large workbooks down.
Check for stray spaces in your criteria cells, as they stop matches from working.
Questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes any number of conditions and puts the sum range first, so the argument order is different.
Can SUMIFS use OR logic?
Not directly. Add two SUMIFS formulas together, or use SUMPRODUCT with a plus sign between conditions, to total rows that match either one.
Why does my SUMIFS return 0?
The criteria probably do not match exactly. Check for extra spaces, numbers stored as text, or a date format mismatch between the cell and the formula.
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.
