SUMIFS and COUNTIFS handle multiple conditions in one formula. Essential for sales analysis, reporting, and conditional calculations.

Basic Syntax

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

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

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

Array formula (Ctrl+Shift+Enter in older Excel):
=SUM(1/COUNTIFS(A:A, A:A, B:B, "West"))

Excel 365:
=SUMPRODUCT(1/COUNTIFS(A2:A100, A2:A100, B2:B100, "West"))

Real-World Dashboards

Sales Summary by Region and Month

Setup:
- Months in row headers (B1:M1)
- Regions in column headers (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)

Advanced Techniques

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 (not built-in AVERAGEIFS):
=SUMIFS(D:D, B:B, "West") / COUNTIFS(B:B, "West")

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

Performance Tips

❌ 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

• #VALUE! → Criteria ranges different sizes or sum_range wrong size
• Wrong totals: Check criteria ranges match sum_range row count
• Returns 0: Criteria not matching (check spaces, case sensitivity with wildcards)
• Missing data: Forgot to use absolute references ($) when copying

Quick Reference

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