Basic Syntax and Differences

TIP: All ranges must be the same size (with matching rows and columns).

Single Criterion — SUMIF

Example 1) Total sales for the “Keyboard” category

=SUMIF(A2:A100, "Keyboard", C2:C100)

Example 2) Amount ≥ 50,000

=SUMIF(C2:C100, ">=50000")

Enter the operator and number as a text string, or join the operator to a cell value: ">="&F2

Multiple Criteria — SUMIFS (AND/OR)

Example 3) Region=Seoul AND product=Keyboard

=SUMIFS(C2:C100, A2:A100, "Keyboard", B2:B100, "Seoul")

Example 4) Product=Keyboard OR Mouse

=SUM(SUMIFS(C2:C100, A2:A100, "Keyboard"),
     SUMIFS(C2:C100, A2:A100, "Mouse"))

Example 5) OR criteria with multiple values (list reference)

=SUMPRODUCT(SUMIFS(C2:C100, A2:A100, F2:F5))

In Microsoft 365:

=SUM(SUMIFS(C2:C100, A2:A100, F2:F5))  // sum spilled results

Date Range Sums (Monthly/Period)

Example 6) 2025-08-01 to 2025-08-31

=SUMIFS(C2:C100, B2:B100, ">="&DATE(2025,8,1), B2:B100, "<="&DATE(2025,8,31))

Example 7) Total for the month of the reference date in F2

=SUMIFS(C:C, B:B, ">="&EOMONTH(F2,-1)+1, B:B, "<="&EOMONTH(F2,0))

Example 8) Last business day of the month only (bonus)

=SUMIFS(C:C, B:B, WORKDAY(EOMONTH(B:B,0),-1))  // array caution

In practice, the most reliable approach is to create month or quarter labels in a helper column and summarize them.

Include or Exclude Text and Wildcards

Example 9) Products containing “Pro”

=SUMIFS(C2:C100, A2:A100, "*Pro*")

Example 10) Exclude “Refurbished”

=SUMIFS(C2:C100, D2:D100, "<>*Refurbished*")

Example 11) Codes with exactly three characters (the ? wildcard)

=SUMIFS(C2:C100, A2:A100, "???")

Tables, Dynamic Arrays, and Alternative Formulas

Example 12) Table (recommended) — readability and automatic ranges

=SUMIFS(Sales[Amount], Sales[Item], "Keyboard", Sales[Region], "Seoul")

Example 13) Alternative calculation with FILTER (Microsoft 365)

=SUM(FILTER(C2:C100, (A2:A100="Keyboard")*(B2:B100="Seoul")))

Example 14) Reuse formulas and improve performance with LET

=LET(it,A2:A100, rg,B2:B100, val,C2:C100,
  SUMIFS(val, it,"Keyboard", rg,"Seoul"))

Mistakes, Errors, and Performance Tips

• Mismatched range sizes → Make the row and column counts the same for all ranges.
• Numbers stored as text → Convert them with VALUE or standardize the source format.
• Blank cells vs. 0 → The criterion "=" matches blank cells only; =0 matches cells with a value of 0.
• Overusing whole-column references → Use exact ranges or tables (for better speed).
• Case sensitivity → SUMIF/SUMIFS are not case-sensitive. If needed, use SUMPRODUCT(EXACT(...)*value).

Summary

FAQ