SUMIFS and COUNTIFS answer the questions every report asks: “How much did the North region sell in March?” or “How many orders are still pending?” Once you understand their simple pattern, you will use them every day. Here is the pattern and real examples you can copy.
Quick answer
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] …)adds numbers that meet all conditions.=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2] …)counts rows that meet all conditions.
Every condition is a pair: where to look, and what to look for.
Sample data
| A: Date | B: Region | C: Product | D: Status | E: Amount |
|---|---|---|---|---|
| 05/03/2026 | North | Laptop | Paid | 650 |
| 12/03/2026 | South | Monitor | Pending | 210 |
| 18/03/2026 | North | Monitor | Paid | 195 |
| 02/04/2026 | North | Laptop | Pending | 720 |
| 09/04/2026 | East | Printer | Paid | 150 |
The examples assume this data is in rows 2–6.
SUMIFS examples
1. Total for one region
=SUMIFS(E2:E6, B2:B6, "North") → 1,565
2. Two conditions: region and product
=SUMIFS(E2:E6, B2:B6, "North", C2:C6, "Laptop") → 1,370
3. Use cells instead of typing conditions
Put “North” in H2 and “Laptop” in H3, then: =SUMIFS(E2:E6, B2:B6, H2, C2:C6, H3). Change H2 and the result updates. This is how report tables are built.
4. Between two dates (one month)
With the first day of the month in H5 (01/03/2026):
=SUMIFS(E2:E6, A2:A6, ">="&H5, A2:A6, "<="&EOMONTH(H5,0)) → 1,055 (March)
The & joins the operator to the date. EOMONTH gives the last day of the month.
5. “Not equal to”
=SUMIFS(E2:E6, D2:D6, "<>Paid") → 930 (everything not yet paid)
6. Greater than a number
=SUMIFS(E2:E6, E2:E6, ">500") → 1,370 (only big orders)
COUNTIFS examples
7. Count pending orders
=COUNTIFS(D2:D6, "Pending") → 2
8. Count pending orders in one region
=COUNTIFS(D2:D6, "Pending", B2:B6, "North") → 1
9. Count orders in a date range
=COUNTIFS(A2:A6, ">="&H5, A2:A6, "<="&EOMONTH(H5,0)) → 3
10. Text that contains a word
Wildcards: * means “anything”. =COUNTIFS(C2:C6, "*top*") counts products containing “top”, such as Laptop → 2.
Build a summary table
List regions down column H and months across row 1 of the summary, then use one formula and copy it across and down:
=SUMIFS($E:$E, $B:$B, $H2, $A:$A, ">="&I$1, $A:$A, "<="&EOMONTH(I$1,0))
The $ signs keep the right parts fixed when you copy. Press F4 while editing to add them. Our budget and inventory templates use this exact technique.
Related functions
| Function | What it does |
|---|---|
| AVERAGEIFS | Average of values that meet conditions |
| MAXIFS / MINIFS | Largest or smallest value that meets conditions (Excel 2019 and later) |
| SUMIF / COUNTIF | Older one-condition versions; note SUMIF puts the sum range last |
Common mistakes
- Ranges of different sizes: every range must cover the same rows, or you get
#VALUE!. - Missing quotes: text and operators go in quotes:
">500","North". - Dates stored as text: date conditions will not match. Make sure dates are real dates.
- Extra spaces: “North ” is not “North”. Clean data with
TRIM. - Expecting OR logic: SUMIFS uses AND (all conditions). For “North OR South”, add two SUMIFS together.
FAQ
Do SUMIFS and COUNTIFS work in Google Sheets?
Yes, with the same syntax.
Can I use whole columns like B:B?
Yes, and it is convenient for growing data. In very large files it can be slower; Excel Tables are a good alternative.