How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria), by Leila Gharani on YouTube. 14 min 4 s.
- Video length
- 14 min 4 s
- Time to do it
- 25 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Lay out your data with one header row. Put the numbers you want to total in one column and the labels you will filter on, such as product, month and country, in the columns beside it.
Start with SUMIF for a single condition. Type =SUMIF( then select the criteria column, add a comma, enter the criterion in quotes, add a comma and select the sum column.
Switch to SUMIFS for two conditions. Type =SUMIFS( then select the sum range first, followed by the first criteria range and its criterion, then the second criteria range and its criterion.
Add a third criterion by typing another comma, the third range and the third criterion. Keep adding range and criterion pairs in the same pattern for each extra condition.
Use wildcards inside the quotes to match partial text. An asterisk stands for any number of characters, so "*phone*" matches any cell containing the word phone.
Add comparison operators for numbers. Write the operator and value inside quotes, like ">1000", and put the cell reference outside the quotes when comparing to a cell.
Remember that operators and text criteria need quotation marks. Without them Excel returns an error or the wrong total, so check each criterion is wrapped correctly.
Swap SUMIFS for AVERAGEIFS to get the average of matching rows. The argument order is identical: average range first, then the range and criterion pairs.
Use COUNTIF for one condition and COUNTIFS for several. COUNTIFS takes only range and criterion pairs, with no sum range at the start.
Convert your data range to an Excel Table with Ctrl+T. Formulas that reference the table name then pick up new rows automatically as you add them.
Tips and common mistakes
Select the sum range first in SUMIFS, not the criteria range, or the formula will total the wrong column.
All ranges in one SUMIFS must be the same size, or Excel returns a #VALUE error.
Use absolute references or a table name so you can copy the formula down without the ranges shifting.
Wildcards only work with text criteria, not with numbers or dates.
Questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes two or more conditions and puts the sum range first, so it is worth learning SUMIFS from the start.
Why does my SUMIFS formula return zero?
The most common causes are mismatched range sizes, missing quotation marks around text or operators, or criteria that do not exactly match the data, such as extra spaces.
How many criteria can SUMIFS handle?
SUMIFS, COUNTIFS and AVERAGEIFS accept up to 127 range and criteria pairs, which is far more than most worksheets ever need.
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.
