SUMIF With Multiple Criteria: Complete Guide

Q: How do I SUMIF with two conditions in Excel?

Use SUMIFS: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Example: =SUMIFS(D:D,A:A,East,B:B,Paid) sums column D where column A is East AND column B is Paid.

Q: How do I sum values from the last 30 days with SUMIFS?

Use SUMIFS with a date condition: =SUMIFS(D:D, A:A, >=&(TODAY()-30)). This sums column D where the date in column A is within the last 30 days. The ampersand joins the comparison operator with the dynamic date.

Q: Can SUMIF handle partial text matches?

Yes. Use wildcards: =SUMIF(A:A,*apple*,B:B) sums column B wherever column A contains the word apple. The asterisk matches any characters before or after. SUMIFS supports the same wildcard syntax.

Q: What is the difference between SUMIF and SUMPRODUCT?

SUMIF and SUMIFS are faster and simpler for standard conditions. SUMPRODUCT is more flexible and handles OR logic and complex array calculations. Use SUMIFS for most tasks and switch to SUMPRODUCT when you need OR conditions that SUMIFS cannot express.

SUMIF vs SUMIFS: Which to Use

SUMIF sums values meeting one condition. SUMIFS sums values meeting multiple conditions simultaneously. Use SUMIFS for almost everything — it handles one condition just as well as SUMIF, and the argument order is more consistent.

SUMIF syntax (one condition)

=SUMIF(range, criteria, sum_range)

Example: =SUMIF(B:B,"East",D:D)
Sum column D where B is "East"

SUMIFS syntax (multiple conditions)

=SUMIFS(sum_range, range1, criteria1, range2, criteria2)

Note: sum_range comes FIRST in SUMIFS but LAST in SUMIF

SUMIFS With Two Conditions

Sum where region=East AND status=Paid

=SUMIFS(C:C, A:A, "East", B:B, "Paid")

You can add as many criteria pairs as you need — SUMIFS supports up to 127 conditions.

SUMIFS With Date Ranges

Date conditions require the & operator to join the comparison sign with the date value:

Last 30 days

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

Between two specific dates

=SUMIFS(D:D, A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,12,31))

Current month only

=SUMIFS(D:D, A:A, ">="&EOMONTH(TODAY(),-1)+1, A:A, "<="&EOMONTH(TODAY(),0))

SUMIF Containing Specific Text (Wildcard)

Use asterisk * as a wildcard to match partial text:

Sum where cell CONTAINS a word

=SUMIF(A:A, "*apple*", B:B) ← matches "apple", "Apple Inc", "pineapple" =SUMIF(A:A, "apple*", B:B) ← starts with "apple" =SUMIF(A:A, "*apple", B:B) ← ends with "apple"

SUMIF With OR Logic (Either Condition)

SUMIFS uses AND logic — all conditions must match. For OR logic, add separate SUMIF results:

Sum where region is East OR West

=SUMIF(A:A,"East",C:C) + SUMIF(A:A,"West",C:C)

SUMPRODUCT for OR (avoids double-counting)

=SUMPRODUCT(((A2:A100="East")+(A2:A100="West")>0)*(C2:C100))

SUMIF Common Mistakes

Argument order: SUMIF is (range, criteria, sum_range) — SUMIFS is (sum_range, range, criteria). Easy to mix up
Date conditions without &: ">="DATE(2024,1,1) fails — must be ">="&DATE(2024,1,1)
Ranges must be the same size: SUMIFS(D:D, A2:A100, "East") will error — use matching full columns
Text criteria must be in quotes: SUMIF(A:A, Paid, B:B) fails — use "Paid"

FAQ

Frequently Asked Questions

How do I SUMIF with two conditions in Excel?▾

Use SUMIFS — it handles multiple conditions: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Example: =SUMIFS(D:D,A:A,"East",B:B,"Paid") sums column D where column A is East AND column B is Paid.

How do I sum values from the last 30 days with SUMIFS?▾

=SUMIFS(D:D, A:A, ">="&(TODAY()-30)) — sums column D where the date in column A is within the last 30 days. The & operator joins the comparison sign with the dynamic date value. Ensure column A is formatted as dates, not text.

Can SUMIF handle partial text matches?▾

Yes — use wildcards: =SUMIF(A:A,"*apple*",B:B) sums column B wherever column A contains the word apple. The * matches any characters. SUMIFS supports the same wildcard syntax.

What is the difference between SUMIF and SUMPRODUCT?▾

SUMIF/SUMIFS are faster and simpler for straightforward conditions. SUMPRODUCT is more flexible — it supports OR logic, complex array calculations, and works in all Excel versions. Use SUMIFS for most tasks; switch to SUMPRODUCT when you need OR conditions or logic SUMIFS cannot express.

SUMIF With Multiple Criteria: SUMIFS, OR Logic & Date Ranges