Useful Data Tips
Home / Excel Tips / SUMIFS & COUNTIFS

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

⏱️ 33 sec read 📊 Excel

SUMIF and SUMIFS add up numbers; COUNTIF and COUNTIFS count cells. The versions ending in S accept multiple conditions in one formula, and every condition must be true for a row to be included. That makes SUMIFS and COUNTIFS the standard tools for sales summaries, reporting, and any calculation that depends on more than one criterion.

SUMIF vs COUNTIF (and SUMIFS vs COUNTIFS): What's the Difference?

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.

Basic Syntax

Each function has a single-condition version and a multiple-condition version ending in S. Watch where the range to add goes: it is the last argument in 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, ...)

SUMIFS Examples

SUMIFS adds the values in sum_range for rows where every criteria range matches its criteria. 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.

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

Pro Tip: Build complex SUMIFS step by step. Start with one condition, verify it works, then add conditions one at a time. This makes debugging much easier than writing the whole formula at once.

← Back to Excel Tips

Related Tips

Written by Aaron Pitt · Useful Data Tips