SUMIFS, COUNTIFS and AVERAGEIFS in Excel with Examples
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016
A plain SUM adds every number in a range. In real work you usually need something narrower: sales for one branch, the number of unpaid invoices, or the average order value for a single month. The conditional functions SUMIFS, COUNTIFS and AVERAGEIFS do exactly that. They look at one or more columns, keep only the rows that match your rules, and then add, count or average them. They are fast, they update automatically and, for many reports, they are simpler than a pivot table.
The example data
Imagine a sales list with a header row and these columns: A = Date, B = Branch (Dubai, Sharjah, Ajman), C = Salesperson, D = Product, E = Amount, F = Status (Paid or Unpaid). The data runs from row 2 to row 500. Turning the list into an Excel Table first is a good habit, because the formulas then grow automatically as you add rows, but ordinary ranges work too.
Counting with COUNTIF and COUNTIFS
COUNTIF counts cells that meet one condition:
=COUNTIF(B2:B500,"Dubai")
This returns the number of sales made in the Dubai branch. COUNTIFS takes pairs of range and condition, and counts only rows where all the conditions are met:
=COUNTIFS(B2:B500,"Dubai",F2:F500,"Unpaid")
That counts unpaid sales in Dubai. You can add up to 127 pairs, although two or three is typical. Every range must be the same size; B2:B500 and F2:F400 together return #VALUE!.
Adding with SUMIF and SUMIFS
SUMIFS puts the column to add first, followed by the range and condition pairs:
=SUMIFS(E2:E500,B2:B500,"Dubai")
=SUMIFS(E2:E500,B2:B500,"Dubai",F2:F500,"Paid")
The first formula totals all Dubai sales; the second totals only the paid ones. The older SUMIF handles a single condition but puts the sum range last: =SUMIF(B2:B500,"Dubai",E2:E500). Because the argument order differs, many people simply use SUMIFS for everything, even with one condition. That way you only have to remember one pattern.
Averages with AVERAGEIFS, MAXIFS and MINIFS
AVERAGEIFS follows the same pattern as SUMIFS:
=AVERAGEIFS(E2:E500,C2:C500,"Fatima")
This returns Fatima's average sale. If no rows match, AVERAGEIFS returns #DIV/0!, which you can replace with =IFERROR(AVERAGEIFS(...),0). In Excel 2019 and later, MAXIFS and MINIFS find the largest and smallest values that meet the conditions, for example the biggest single sale in Sharjah: =MAXIFS(E2:E500,B2:B500,"Sharjah").
Writing criteria: text, numbers, cell references and wildcards
The condition is always written as text, which gives you a lot of flexibility:
- Exact text:
"Dubai". Matching is not case-sensitive. - Numbers with operators:
">1000","<=500","<>0". - A value in another cell: type the cell reference without quotes,
H2. To combine an operator with a cell, join them with &:">"&H2. - Not equal to some text:
"<>Cancelled". - Blank and non-blank cells:
""counts empty cells and"<>"counts filled ones. - Wildcards: an asterisk matches any number of characters and a question mark matches exactly one.
"*laptop*"matches any product containing the word laptop;"INV-2026-???"matches invoice numbers with exactly three characters at the end.
Putting your criteria in cells, rather than typing them into the formula, makes a report reusable. Change the branch name in H1 and every formula that refers to H1 recalculates.
Totals between two dates
To total sales for a period, use the date column twice: once for the start and once for the end.
=SUMIFS(E2:E500,A2:A500,">="&H1,A2:A500,"<="&H2)
Put the start date in H1 and the end date in H2. For a whole month, a neat trick is to use EOMONTH: with the first day of the month in H1, the end condition becomes "<="&EOMONTH(H1,0). If your dates include times, use "<"&H2+1 for the end condition so that sales made during the last day are included. Our guide to date and time functions covers EOMONTH and related functions in more detail.
Building a summary table
A common report lists branches down the side and months across the top, with a SUMIFS formula in every cell. In the grid, the branch names sit in column H (H2:H4) and the first day of each month sits in row 1 (I1:T1). In I2 enter:
=SUMIFS($E$2:$E$500,$B$2:$B$500,$H2,$A$2:$A$500,">="&I$1,$A$2:$A$500,"<="&EOMONTH(I$1,0))
The dollar signs lock the data ranges, lock column H for the branch and lock row 1 for the month, so you can copy the single formula across and down the whole grid. If the dollar signs are new to you, read absolute and relative references first. Add a SUM row and column for totals and the report is finished.
Common mistakes to avoid
- Ranges of different sizes. Every range in SUMIFS and COUNTIFS must cover the same rows.
- Numbers stored as text. Amounts imported from a bank or another system may be text, which SUMIFS ignores. Look for a green triangle in the cell corner and convert with Data > Text to Columns > Finish.
- Hidden spaces in labels. "Dubai " with a trailing space will not match "Dubai". Clean the column with TRIM.
- Expecting OR logic. SUMIFS joins conditions with AND. For Dubai or Sharjah, add two SUMIFS together, or use
=SUM(SUMIFS(E2:E500,B2:B500,{"Dubai","Sharjah"})). - Forgetting the & with operators.
">H1"looks for the text H1; you need">"&H1.
Summary
- COUNTIFS counts, SUMIFS adds and AVERAGEIFS averages rows that meet every condition.
- SUMIFS and AVERAGEIFS put the range to calculate first; SUMIF puts it last.
- Criteria are text: use quotes, operators, & with cell references and wildcards.
- Use two conditions on the date column to total a period.
- Lock ranges with dollar signs to copy one formula across a whole summary grid.
Back to: all Excel tutorials.