Workflow guide
SUMIFS vs COUNTIFS
Use SUMIFS when the answer should be a total from a numeric column. Use COUNTIFS when the answer should be how many rows match the same criteria.
Which formula should you use?
| Situation | Use | Why |
|---|---|---|
| You need total sales, cost, inventory value, or hours | SUMIFS | SUMIFS adds a numeric range after matching one or more criteria ranges. |
| You need the number of completed tasks, orders, rows, or records | COUNTIFS | COUNTIFS counts rows that match the criteria and does not need an amount column. |
| You need a monthly total | SUMIFS | Use a date range boundary and sum the amount column for the target month. |
| You need a monthly row count | COUNTIFS | Use the same date boundary pattern but count matching date rows instead of summing values. |
| You need the matching records themselves | FILTER or QUERY | SUMIFS and COUNTIFS return one number. FILTER and QUERY return the rows behind that number. |
| You need an average for matching rows | AVERAGEIFS | AVERAGEIFS uses criteria like SUMIFS, but returns an average instead of a sum or count. |
Sample data
| Date | Region | Product | Amount |
|---|---|---|---|
| 2026-01-04 | East | Widget | 420 |
| 2026-01-12 | West | Widget | 310 |
| 2026-02-03 | East | Gadget | 275 |
| 2026-02-15 | East | Widget | 640 |
Copyable formulas
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")This returns a total from the Amount column.
=COUNTIFS(B2:B100, "East", C2:C100, "Widget")This returns how many rows match the criteria.
=SUMIFS(D2:D100, A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Use this when F1 contains any real date in the month you want to summarize.
=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Use this when the report needs how many rows occurred in the selected month.
Same criteria different result
The criteria can be identical while the returned number changes. SUMIFS adds the Amount values from matching rows. COUNTIFS counts the matching rows themselves.
=SUMIFS(D2:D100, B2:B100, "East", C2:C100, "Widget")Returns 1060 from the sample rows: 420 plus 640.
=COUNTIFS(B2:B100, "East", C2:C100, "Widget")Returns 2 because two rows match East and Widget.
Date criteria and month reports
For month reports, use the same date boundary in both functions. The difference is the final result: SUMIFS totals the amount column, while COUNTIFS counts the date rows.
Use a real date in F1 and compare dates with >= month start and < first day of next month. This avoids month-name text problems and timestamp edge cases.
Excel and Google Sheets support
SUMIFS and COUNTIFS use the same criteria-pair syntax in Excel and Google Sheets for these examples. The main requirement is that every criteria range covers the same rows as the range being summed or counted.
Returned results from the sample data
| Question | Formula to use | Returned result |
|---|---|---|
| What is the total East Widget amount? | SUMIFS | 1060 |
| How many East Widget rows are there? | COUNTIFS | 2 |
| What is the January 2026 amount? | SUMIFS by month | 730 |
| How many January 2026 rows are there? | COUNTIFS by month | 2 |
| What is the February East row count? | COUNTIFS with month plus region | 2 |
Formula argument differences
| Function | First argument | Then add | Returns |
|---|---|---|---|
| SUMIFS | The numeric range to add, such as D2:D100 | Criteria range and criteria pairs | A total amount, quantity, cost, or hours value |
| COUNTIFS | The first criteria range, such as B2:B100 | Additional criteria range and criteria pairs | A row count |
How the workflow fits together
- SUMIFS and COUNTIFS share the same criteria-pair idea, but SUMIFS starts with the numeric range to add.
- COUNTIFS has no sum range because it counts matching rows directly.
- Both functions work in Excel and Google Sheets for normal criteria, operator criteria, and date boundaries.
- Use the builders when you need to change ranges, add optional criteria, or copy a version with safe criteria quoting.
Common mistakes
- Do not use COUNTIFS when the expected result is a dollar amount, quantity total, or inventory value.
- Do not use SUMIFS without a numeric sum range.
- All criteria ranges must have the same shape as the sum range or count range.
- For month reports, compare real date values with >= month start and < next month.
Related formulas
FAQ
Does COUNTIFS add values?
No. COUNTIFS counts rows. Use SUMIFS when you need to add an amount, quantity, cost, or hours column.
Do SUMIFS and COUNTIFS work in Google Sheets?
Yes. The basic criteria-pair syntax works in both Excel and Google Sheets.
Which function should I use for a dashboard total?
Use SUMIFS when the dashboard card should show a total amount. Use COUNTIFS when the card should show how many rows match the filters.
Can SUMIFS and COUNTIFS use the same criteria?
Yes. Region, product, status, and date criteria can be the same. The difference is that SUMIFS adds a numeric range while COUNTIFS counts matching rows.