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:

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

Summary

Back to: all Excel tutorials.