Adding up a column is easy. Adding up only the amounts of a certain agent, in a certain area, in a certain month, is the kind of question that comes up every day in an office. The SUMIFS, COUNTIFS and AVERAGEIFS functions (SOMMA.PIÙ.SE, CONTA.PIÙ.SE and MEDIA.PIÙ.SE in Italian) answer exactly this: they calculate a total, a count or an average considering only the rows that meet every condition you specify.
The syntax
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)
The first argument is the column to add up; then come pairs of "where to look" and "what must be there". The conditions are all combined with AND: a row is added only if it meets them all.
COUNTIFS has the same structure but without the first argument, because it counts rows instead of adding them:
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], ...)
AVERAGEIFS follows the SUMIFS pattern.
There are also single-condition versions, SUMIF and COUNTIF. Watch out: in SUMIF the range to add up is the third argument, not the first. To avoid confusion many people prefer to always use the IFS version, even with one condition.
A basic example
With a sales list holding the date in A, the agent in B, the area in C and the amount in D:
=SUMIFS($D$2:$D$5000, $B$2:$B$5000, "Bianchi", $C$2:$C$5000, "North")
returns the total sold by Bianchi in the North area. All the ranges must be the same size: if one runs from 2 to 5000 and another from 2 to 4999, the function returns #VALUE!.
How to write criteria
Criteria are the part that causes the most doubts. The rules are few:
| Criterion | Meaning |
|---|---|
"North" |
equal to North (not case sensitive) |
">1000" |
greater than 1000 |
"<>Cancelled" |
not equal to Cancelled |
"<>" |
cell not empty |
"" |
cell empty |
"Ross*" |
text starting with Ross |
"???-2*" |
any three characters, a hyphen, then 2 and more |
When the comparison value is in a cell, the operator is joined with an ampersand:
=COUNTIFS($D$2:$D$5000, ">"&G1)
Writing ">G1" in quotes is a common mistake: the function would look for the text "G1", not the value of the cell.
Conditions on dates
Dates are numbers, so they are compared with the same operators. For the March 2026 total, with the date in A:
=SUMIFS($D$2:$D$5000, $A$2:$A$5000, ">="&DATE(2026,3,1), $A$2:$A$5000, "<"&DATE(2026,4,1))
Using "less than the first day of the following month" instead of "less than or equal to the last day" has an advantage: it works even if the cells contain a time as well as a date, and you do not need to know how many days the month has.
Avoid writing the date as text inside the criterion, for example ">=01/03/2026": the result depends on the computer's regional settings and may change when the file is opened elsewhere. DATE() is always unambiguous.
A monthly summary that updates itself
Here is a layout I use often. In column F write the first day of each month (01/01/2026, 01/02/2026 and so on), formatted to show only month and year. In cell G1 type an agent's name. In G2:
=SUMIFS($D$2:$D$5000, $B$2:$B$5000, G$1, $A$2:$A$5000, ">="&$F2, $A$2:$A$5000, "<"&EOMONTH($F2,0)+1)
EOMONTH returns the last day of the month; adding 1 gives the first day of the next month. Copying the formula down gives you sales month by month; copying it to the right, with other agents' names in H1, I1 and so on, gives you a complete grid. The mixed references G$1 and $F2 make each cell read the right agent and the right month.
OR conditions
The IFS functions combine conditions with AND. For an OR condition, for example sales in the North or Centre areas, the simplest solution is to add two results:
=SUMIFS($D$2:$D$5000, $C$2:$C$5000, "North") + SUMIFS($D$2:$D$5000, $C$2:$C$5000, "Centre")
With many alternatives, it is better to add a grouping column to the data (for example "Macro-area") and use that as the criterion.
Frequent mistakes
- Ranges of different sizes, which produce #VALUE!.
- Operator and reference in the same string, as in
">G1". - Spaces in the data: "North " with a trailing space does not match "North".
- Amounts stored as text, which SUMIFS ignores without warning: the total simply comes out lower.
- Unlocked ranges that slide when you copy the formula.
In my work, when a total obtained with SUMIFS does not match a pivot built on the same data, the cause is almost always one of the last two points.
In short
- SUMIFS adds only the rows that meet every condition; COUNTIFS counts them.
- All the ranges must be the same size.
- With a value in a cell, join operator and reference:
">"&G1. - For dates use DATE() and the range "from the first day to before the next month".
- Conditions are combined with AND; for OR add several results or add a grouping column.
