SUMIFS vs COUNTIFS in Excel: Differences, Syntax, and Examples

SUMIFS adds up the values in the rows that meet every condition you set, while COUNTIFS counts how many rows meet them. Both take pairs of criteria ranges and criteria and include a row only when all of the conditions are true; SUMIFS also needs a sum_range, the column to add, which goes first. SUMIF and COUNTIF are the single-condition versions of the same pair.

What Is the Difference Between SUMIF and COUNTIF?

COUNTIF returns how many cells meet a condition, while SUMIF returns the total of the values in the rows that meet it. COUNTIF only needs the range to test and the criteria. SUMIF also takes a sum_range, the column of numbers to add; if you leave it out, SUMIF adds the tested range itself. SUMIFS and COUNTIFS work the same way but accept up to 127 range/criteria pairs, and a row only counts if all of them match.

Data: B2:B100 = Region, C2:C100 = Status, D2:D100 = Sales

How many West orders? (a count of rows)
=COUNTIF(B2:B100, "West")

What were total West sales? (a total of column D)
=SUMIF(B2:B100, "West", D2:D100)

Same questions with two conditions (West AND Active):
=COUNTIFS(B2:B100, "West", C2:C100, "Active")
=SUMIFS(D2:D100, B2:B100, "West", C2:C100, "Active")

Function	Returns	Conditions
COUNTIF	Number of cells that match	One
SUMIF	Sum of the values in matching rows	One
COUNTIFS	Number of rows where every condition matches	Up to 127 pairs
SUMIFS	Sum of the values where every condition matches	Up to 127 pairs

Rule of thumb: if the question is "how many," use COUNTIF or COUNTIFS; if it is "how much," use SUMIF or SUMIFS. All four share the same criteria syntax (quoted operators, wildcards, cell references joined with &), so the criteria in every example below work the same way in each of them. For the argument-order trap between the single and multiple-condition versions, see SUMIF vs SUMIFS.

What Is the Syntax for SUMIFS and COUNTIFS?

SUMIFS is written =SUMIFS(sum_range, criteria_range1, criteria1, ...) and COUNTIFS is written =COUNTIFS(criteria_range1, criteria1, ...): the same range/criteria pairs, with no sum_range in COUNTIFS. Watch where the range to add goes: it is the last argument in single-condition SUMIF but the first argument in SUMIFS.

SUMIF vs SUMIFS

SUMIF (single condition):
=SUMIF(range, criteria, sum_range)

SUMIFS (multiple conditions):
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Note: sum_range comes FIRST in SUMIFS (opposite of SUMIF!)

COUNTIF vs COUNTIFS

COUNTIF (single condition):
=COUNTIF(range, criteria)

COUNTIFS (multiple conditions):
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

How Do You Use SUMIFS With Multiple Criteria?

Put the column to add first, then one criteria range and criteria for each condition: =SUMIFS(D:D, B:B, "West", C:C, "Active") totals column D only for rows where column B is West and column C is Active. Criteria can be text, numbers, comparison operators, dates, or wildcards, and every range must be the same size.

Two Conditions (AND Logic)

Sum sales where Region = "West" AND Status = "Active"

=SUMIFS(D:D, B:B, "West", C:C, "Active")

D:D = sum this column (Sales Amount)
B:B = first criteria range (Region)
"West" = first criteria
C:C = second criteria range (Status)
"Active" = second criteria

Using Cell References

=SUMIFS($D$2:$D$100, $B$2:$B$100, F2, $C$2:$C$100, G2)

F2 = selected region
G2 = selected status

$ locks ranges when copying formula
No $ on F2, G2 lets them change per row

Date Ranges

Sum sales after a specific date:
=SUMIFS($D$2:$D$100, $A$2:$A$100, ">" & DATE(2024,1,1))

Between two dates:
=SUMIFS($D$2:$D$100,
    $A$2:$A$100, ">=" & DATE(2024,1,1),
    $A$2:$A$100, "<=" & DATE(2024,12,31))

Current month only:
=SUMIFS($D$2:$D$100,
    $A$2:$A$100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
    $A$2:$A$100, "<" & DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1))

Comparison Operators

Greater than:
=SUMIFS(D:D, D:D, ">1000")

Greater than or equal:
=SUMIFS(D:D, C:C, ">=100")

Not equal:
=SUMIFS(D:D, B:B, "<>Pending")

Wildcards (* and ?):
=SUMIFS(D:D, B:B, "John*")    Starts with "John"
=SUMIFS(D:D, B:B, "*Inc")     Ends with "Inc"
=SUMIFS(D:D, B:B, "*Services*") Contains "Services"

COUNTIFS Examples

COUNTIFS follows the same criteria rules as SUMIFS but has no sum_range: it returns the number of rows where every condition is true.

Count Rows Meeting Multiple Criteria

Count customers from West region with Active status:
=COUNTIFS(B:B, "West", C:C, "Active")

Count orders above $500 in Q1:
=COUNTIFS(D:D, ">500", A:A, ">=1/1/2024", A:A, "<=3/31/2024")

Count Blanks and Non-Blanks

Count blanks in column B where column A = "Active":
=COUNTIFS(A:A, "Active", B:B, "")

Count non-blanks:
=COUNTIFS(A:A, "Active", B:B, "<>")

Count Unique Values with Criteria

Count distinct customers (column A) in the West region (column B):
=SUMPRODUCT((B2:B100="West") * (A2:A100<>"")
    / COUNTIFS(A2:A100, A2:A100&"", B2:B100, B2:B100&""))

Works in any Excel version without Ctrl+Shift+Enter.
&"" stops blank cells from causing #DIV/0!,
and (A2:A100<>"") leaves blank customers out of the count.

In Excel 365 or 2021, UNIQUE and FILTER are easier to read; see count unique values in Excel.

Real-World Dashboards

Point SUMIFS and COUNTIFS at an Excel Table and use mixed references ($A2, B$1) for the criteria cells, and one formula can fill an entire summary grid. Copy and paste it across rather than dragging the fill handle: filling right shifts table column references such as Sales[Amount] to the next column unless you write them as Sales[[Amount]:[Amount]].

Sales Summary by Region and Month

Setup:
- Months across row 1 (B1:M1)
- Regions down column A (A2:A5)

Formula in B2:
=SUMIFS(Sales[Amount],
    Sales[Region], $A2,
    Sales[Month], B$1)

Copy across and down for full summary table

YTD Sales vs Target

YTD Actual:
=SUMIFS(Sales[Amount],
    Sales[Date], ">=" & DATE(YEAR(TODAY()), 1, 1),
    Sales[Date], "<=" & TODAY())

YTD Budget:
=SUMIFS(Budget[Amount],
    Budget[Month], ">=" & DATE(YEAR(TODAY()), 1, 1),
    Budget[Month], "<=" & TODAY())

Achievement %:
=YTD_Actual / YTD_Budget

Customer Segmentation

High Value Active Customers:
=COUNTIFS(Customers[Status], "Active",
    Customers[Lifetime_Value], ">10000",
    Customers[Last_Purchase], ">=" & TODAY()-365)

Combining Multiple SUMIFS (OR Logic)

SUMIFS uses AND logic. For OR logic, add multiple SUMIFS:

Sum where Region = "West" OR Region = "East":
=SUMIFS(D:D, B:B, "West") + SUMIFS(D:D, B:B, "East")

Better with SUMPRODUCT:
=SUMPRODUCT((B:B="West")+(B:B="East"), D:D)

How Do You Use SUM and COUNTIFS Together?

Wrap COUNTIFS (or SUMIFS) in SUM when you need several counts added into one number. The most common case is OR logic: give COUNTIFS an array constant of criteria in curly braces, it returns one count per item, and SUM adds those counts together.

Count rows where Region is West OR East:
=SUM(COUNTIFS(B2:B100, {"West","East"}))

Sum sales where Region is West OR East:
=SUM(SUMIFS(D2:D100, B2:B100, {"West","East"}))

West OR East, AND Status = "Active":
=SUM(COUNTIFS(B2:B100, {"West","East"}, C2:C100, "Active"))

Every combination of two OR lists (West/East x Active/Pending):
=SUM(COUNTIFS(B2:B100, {"West","East"}, C2:C100, {"Active";"Pending"}))

In the last formula (US regional settings), the comma makes the first list a row and the semicolon makes the second a column, so all four combinations are counted. If both lists used commas, Excel would pair them item by item (West with Active, East with Pending) instead.

Adding and Weighting Separate Counts

You can also add the results of separate COUNTIFS formulas and weight one of them. For example, in an attendance sheet where "Off" is a full day off and any code starting with "H" is a half day:

=COUNTIFS(B9:B39, "Off") + COUNTIFS(B9:B39, "H*") / 2

Same result written with SUM:
=SUM(COUNTIFS(B9:B39, "Off"), COUNTIFS(B9:B39, "H*") / 2)

Both return the same number, because SUM with comma-separated arguments is just another way to write +. Criteria are not case-sensitive, so "off" also matches "Off" and "OFF". The wildcard "H*" matches every entry that starts with H, including words like "Holiday", so use an exact code such as "H" if other entries share that first letter.

Advanced Techniques

These patterns build the criteria from other cells or functions, so a report updates without anyone editing the formula.

Dynamic Criteria from Dropdown

Cell F1: Data validation dropdown with "All, West, East, North, South"

Formula:
=IF(F1="All",
    SUM(D:D),
    SUMIFS(D:D, B:B, F1)
)

Shows total if "All" selected, filtered total otherwise

SUMIFS with Calculated Criteria

Sum current month sales:
=SUMIFS(D:D,
    A:A, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
    A:A, "<" & EOMONTH(TODAY(), 0) + 1)

Sum last 30 days:
=SUMIFS(D:D, A:A, ">=" & TODAY()-30)

Nested with Other Functions

Average without AVERAGEIFS:
=SUMIFS(D:D, B:B, "West") / COUNTIFS(B:B, "West")

Or just use AVERAGEIFS:
=AVERAGEIFS(D:D, B:B, "West")

Performance Tips

A few SUMIFS or COUNTIFS formulas calculate instantly. Hundreds of them over large data can slow recalculation, and these choices help keep it fast.

❌ Slower	✅ Faster
Entire columns: A:A, B:B	Specific ranges: A2:A10000
Wildcard criteria: "*text*"	Exact match: "text"
Multiple separate formulas	Combined SUMIFS
Non-contiguous ranges	Single contiguous range

Common Errors

Most SUMIFS and COUNTIFS problems come from ranges that do not line up or criteria that look right but do not match the data.

• #VALUE! → Criteria ranges different sizes or sum_range wrong size
• Wrong totals: Check that every range starts and ends on the same rows (D2:D100 with B2:B100, not B1:B99)
• Returns 0: Criteria not matching (check for extra spaces), or the sum_range holds numbers stored as text, which SUMIFS ignores. Matching is not case-sensitive.
• Missing data: Forgot to use absolute references ($) when copying

Quick Reference

The general SUMIFS pattern is below. COUNTIFS is identical without the first argument.

Basic pattern:
=SUMIFS(what_to_sum,
    where_to_check1, what_to_match1,
    where_to_check2, what_to_match2,
    where_to_check3, what_to_match3)

All criteria must be met (AND logic)
All ranges must be same size
Use + to add multiple SUMIFS for OR logic