Excel SUMIFS Formula: 12 Examples, Multiple Criteria & Date Ranges

---

Excel SUMIFS formula syntax: what each argument means

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Argument	Meaning	Example
sum_range	Numeric cells to add	E2:E9
criteria_range1	Cells tested by the first condition	B2:B9
criteria1	Value or rule to match	"UAE"
criteria_range2 / criteria2	Optional additional AND condition	C2:C9,"Search"

All ranges must have matching dimensions. You can use one criterion with SUMIFS, but its main advantage is handling several conditions together. Microsoft’s SUMIFS reference documents up to 127 range/criteria pairs and the supported wildcard characters.

Practice dataset: campaign revenue by market and channel

Enter these values into A1:F9 of a blank Excel sheet. Format column A as dates and column E as numbers. Currency is intentionally unspecified so the arithmetic applies to any reporting currency.

Row	A: Date	B: Market	C: Channel	D: Status	E: Revenue	F: Owner
2	2026-10-01	UAE	Search	Paid	1200	Ali
3	2026-10-02	UAE	Social	Paid	800	Sara
4	2026-10-03	KSA	Search	Paid	1500	Ali
5	2026-10-04	UAE	Search	Organic	300	Sara
6	2026-10-05	UAE	Search	Paid	900	Ali
7	2026-10-06	KSA	Social	Paid	1100	Sara
8	2026-10-07	UAE	Social	Organic	400	Ali
9	2026-10-08	UAE	Search	Paid	600	Sara

Sanity check: total revenue across all eight rows is 6,800. If your formulas produce different values, check whether dates were entered as actual dates and revenue as numbers.

12 SUMIFS examples you can copy and test

1. Total UAE Search revenue using two conditions

=SUMIFS(E2:E9,B2:B9,"UAE",C2:C9,"Search")

Result: 3,000. Adds 1,200 + 300 + 900 + 600. Every matching row must be UAE and Search.

2. Add a third condition: paid UAE Search revenue

=SUMIFS(E2:E9,B2:B9,"UAE",C2:C9,"Search",D2:D9,"Paid")

Result: 2,700. The Organic row worth 300 is excluded.

3. Sum paid revenue for one market

=SUMIFS(E2:E9,B2:B9,"UAE",D2:D9,"Paid")

Result: 3,500. Includes paid Search and paid Social.

4. Sum between two dates, inclusive

=SUMIFS(E2:E9,B2:B9,"UAE",A2:A9,">="&DATE(2026,10,1),A2:A9,"<"&DATE(2026,10,8))

Result: 3,600. This includes 1 through 7 October, and excludes 8 October. Using an exclusive upper boundary is safer than <= the final date when cells may contain hidden time values.

5. Use a greater-than-or-equal revenue condition

=SUMIFS(E2:E9,B2:B9,"UAE",C2:C9,"Search",E2:E9,">=900")

Result: 2,100. Adds only the UAE Search rows with revenue of 1,200 and 900. A numeric criterion may be tested against the same range you are summing.

6. Combine owner and channel

=SUMIFS(E2:E9,F2:F9,"Ali",C2:C9,"Search")

Result: 3,600. Includes Ali’s UAE and KSA Search rows.

7. Exclude a value with not-equal

=SUMIFS(E2:E9,B2:B9,"UAE",D2:D9,"<>Paid")

Result: 700. The two UAE Organic rows total 300 + 400. Note that <> means “not equal to”.

8. Reference input cells instead of typing criteria

Enter UAE in H2 and Search in H3:

=SUMIFS($E$2:$E$9,$B$2:$B$9,H2,$C$2:$C$9,H3)

Result: 3,000. Change H2 or H3 to update the answer. The dollar signs keep the source ranges fixed when the formula is copied.

9. Match text patterns with wildcards

=SUMIFS(E2:E9,B2:B9,"UAE",C2:C9,"S*")

Result: 4,200. In this dataset, both Search and Social start with S. The asterisk matches any sequence of characters. Use ? for exactly one character and ~* to match a literal asterisk.

10. Add two OR alternatives by combining SUMIFS

=SUMIFS(E2:E9,B2:B9,"UAE",C2:C9,"Search")+SUMIFS(E2:E9,B2:B9,"UAE",C2:C9,"Social")

Result: 4,200. Each SUMIFS call uses AND logic internally; adding two calls handles Search or Social. This assumes the two channel labels are mutually exclusive, so a row is not counted twice.

11. Calculate revenue for one market, regardless of channel

=SUMIFS(E2:E9,B2:B9,"KSA")

Result: 2,600. SUMIFS also works with one condition, although SUMIF is another option for that use case.

12. Sum one month without relying on month names

=SUMIFS(E2:E9,A2:A9,">="&DATE(2026,10,1),A2:A9,"<"&DATE(2026,11,1))

Result: 6,800. This captures the full October reporting period, including timestamps on 31 October if the dataset grows. Replace the two dates to calculate another month.

SUMIF vs SUMIFS vs SUMPRODUCT

Function	Best use	Important distinction
SUMIF	One condition	Syntax is SUMIF(range,criteria,[sum_range]).
SUMIFS	Multiple AND conditions	Sum range comes first; criteria ranges must match its dimensions.
SUMPRODUCT	Array-based conditions and calculations	More flexible, but often harder to audit and maintain.

For a typical market, channel and date reporting table, begin with SUMIFS because colleagues can inspect each condition. If you need reusable analysis for large datasets, consider a PivotTable or Power Query rather than filling a workbook with thousands of separate formulas.

Why SUMIFS returns zero or an error

Symptom	Likely cause	What to check
Zero, even when values appear to match	Dates stored as text, extra spaces, wrong criteria	Test ISNUMBER(A2) for a date; compare text values and clean spaces.
#VALUE!	Sum and criteria ranges are different sizes	Keep E2:E9, B2:B9 and other ranges aligned.
Wrong total	Mixed statuses, duplicate rows, or unexpected wildcard matches	Filter the underlying table and inspect matching records.
Date range misses the final day	Cells contain times	Use "<"&next_day as the exclusive upper bound.
Formula does not calculate	Formula cell is formatted as text or separator mismatch	Switch to General and re-enter; some locales use semicolons instead of commas.

Practical checklist before using SUMIFS in a report

• Check the source row count and ensure every range covers the same rows.
• Keep date columns as real Excel dates, not text.
• Use a consistent market and channel naming convention.
• Choose whether you mean AND or OR logic before writing the formula.
• Compare the result with a manually filtered sample before copying the formula across a dashboard.
• Lock the data ranges with $ if you will fill the formula across multiple cells.

How to learn the next Excel skill

Start with the Course Centrals Excel formulas hub to see where SUMIFS fits alongside IF, XLOOKUP and COUNTIFS. For a practical progression from spreadsheet formulas to analytics workflows, explore our free data analysis courses and certificate options. Once you can explain a SUMIFS calculation to someone else, build a small market-by-channel report from your own anonymized data.

---

Frequently asked questions

Can SUMIFS use more than two conditions?

Yes. Add another criteria_range,criteria pair for each condition. Microsoft’s documented limit is 127 pairs.

Does SUMIFS support OR conditions?

Not as separate conditions within a single ordinary SUMIFS call. Use two SUMIFS calculations added together for distinct alternatives, or a carefully designed array formula when appropriate.

Why is SUMIFS different from SUMIF?

SUMIFS puts the sum range first, while SUMIF normally puts the criteria range first and the optional sum range last. Mixing their argument orders is a common mistake.

Does SUMIFS distinguish uppercase from lowercase text?

No. For case-sensitive matching, you need a different approach, such as an appropriately constructed SUMPRODUCT/EXACT formula.

Can I use SUMIFS with dates from input cells?

Yes. For a start date in H4 and end date in H5, use =SUMIFS(E2:E9,A2:A9,">="&H4,A2:A9,"<"&(H5+1)) if H5 is a date-only inclusive endpoint.

Official reference and editorial verification

Primary source: Microsoft Support: SUMIFS function. Formula syntax, wildcard behavior, range-size rules and function limits were checked against the official documentation. All sample values and calculated results in this article are original teaching examples, not industry benchmarks.