Why does my formula show an error?

Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.

Should I copy formulas down or use a Table?

Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.

Formulas - 2026-01-21

SUMIF vs SUMIFS: When to Use Each Function

Master conditional summing in Excel. Learn the difference between SUMIF and SUMIFS with practical examples.

SUMIF vs SUMIFS: Complete Guide

Both SUMIF and SUMIFS add up numbers based on conditions, but they work differently. Understanding when to use each one will make your spreadsheets more powerful and efficient. For more function comparisons, see our Excel Functions: Complete Guide.

Quick Comparison

Feature SUMIF SUMIFS Number of conditions 1 only Multiple (up to 127) Sum range position Last (optional) First (required) Syntax complexity Simpler More verbose When to use Single criterion Multiple criteria Excel version All versions Excel 2007+

SUMIF: Single Condition Summing

Syntax \\\ =SUMIF(range, criteria, [sum_range]) \\\ Arguments: range — The cells to evaluate against your criteria criteria — The condition that determines which cells to sum sum_range — (Optional) The cells to actually add up. If omitted, Excel sums the range itself Basic Examples Example 1: Sum sales for one product \\\ ' Data: A = Product, B = Sales =SUMIF(A:A, "Apple", B:B) ' Sums all sales where product is "Apple" \\\ Example 2: Sum values greater than 100 \\\ =SUMIF(B:B, ">100") ' Sums all values in column B that are greater than 100 ' Note: No sum_range needed when summing the same range \\\ Example 3: Sum using cell reference \\\ ' If D1 contains "Apple" =SUMIF(A:A, D1, B:B) ' More flexible - change D1 to sum different products \\\ SUMIF Criteria Options Criteria Type Example Matches Exact text "Apple" Apple only Number 100 Exactly 100 Greater than ">100" 101, 200, 500... Less than "<50" 49, 25, 10... Greater or equal ">=100" 100, 101, 200... Less or equal "<=50" 50, 49, 25... Not equal "<>Apple" Everything except Apple Wildcard * "App*" Apple, Application, App Wildcard ? "App?e" Apple (? = single char) Cell reference A1 Whatever is in A1 Blank cells "" Empty cells Non-blank "<>" Cells with any content SUMIF with Wildcards \\\ ' Sum all products starting with "App" =SUMIF(A:A, "App*", B:B) ' Matches: Apple, Application, Appetizer ' Sum products with exactly 5 characters =SUMIF(A:A, "?????", B:B) ' Matches: Apple, Grape (5 chars each) ' Sum products containing "berry" =SUMIF(A:A, "*berry*", B:B) ' Matches: Strawberry, Blueberry, Raspberry \\\ SUMIF with Dates \\\ ' Sum sales after a specific date =SUMIF(A:A, ">"&DATE(2024,1,1), B:B) ' Sum sales for today =SUMIF(A:A, TODAY(), B:B) ' Sum sales for a specific month (requires helper column) =SUMIF(MonthColumn, 1, B:B...

SUMIFS: Multiple Condition Summing

Syntax \\\ =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) \\\ Key difference: Sum range comes FIRST in SUMIFS! Arguments: sum_range — The cells to add up (required, comes first) criteria_range1 — First range to evaluate criteria1 — First condition criteria_range2, criteria2 — Additional range/condition pairs (optional) Basic Examples Example 1: Two conditions \\\ ' Sum sales for "Apple" in "January" =SUMIFS(C:C, A:A, "Apple", B:B, "January") ' C = Sales, A = Product, B = Month \\\ Example 2: Three conditions \\\ ' Sum sales for "Apple" in "January" in "West" region =SUMIFS(D:D, A:A, "Apple", B:B, "January", C:C, "West") \\\ Example 3: Numeric conditions \\\ ' Sum sales over $1000 for "Apple" =SUMIFS(B:B, A:A, "Apple", B:B, ">1000") ' Note: B:B appears twice - once as sum_range, once as criteria_range \\\ SUMIFS with Date Ranges \\\ ' Sum sales between two dates =SUMIFS(B:B, A:A, ">="&StartDate, A:A, "<="&EndDate) ' Sum sales for current month =SUMIFS(B:B, A:A, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A:A, "<="&EOMONTH(TODAY(),0)) ' Sum sales for last 30 days =SUMIFS(B:B, A:A, ">="&TODAY()-30, A:A, "<="&TODAY()) \\\ SUMIFS with Multiple OR Conditions SUMIFS uses AND logic (all conditions must be true). For OR logic, use multiple SUMIFS: \\\ ' Sum sales for Apple OR Orange =SUMIFS(B:B, A:A, "Apple") + SUMIFS(B:B, A:A, "Orange") ' Or use SUMPRODUCT for more flexibility =SUMPRODUCT((A:A="Apple")+(A:A="Orange"), B:B) \\\

Common Mistakes and Solutions

Mistake 1: Wrong Argument Order \\\ ' WRONG - SUMIFS with sum_range last (SUMIF style) =SUMIFS(A:A, "Apple", B:B, "January", C:C) ❌ ' CORRECT - Sum range first in SUMIFS =SUMIFS(C:C, A:A, "Apple", B:B, "January") ✓ \\\ Mistake 2: Mismatched Range Sizes \\\ ' WRONG - Different sized ranges =SUMIFS(B1:B100, A1:A50, "Apple") ❌ ' CORRECT - Same sized ranges =SUMIFS(B1:B100, A1:A100, "Apple") ✓ \\\ Mistake 3: Forgetting Quotes Around Operators \\\ ' WRONG - Operator without quotes =SUMIF(A:A, >100, B:B) ❌ ' CORRECT - Operator in quotes =SUMIF(A:A, ">100", B:B) ✓ \\\ Mistake 4: Criteria with Cell References \\\ ' WRONG - Operator and cell reference =SUMIF(A:A, ">D1", B:B) ❌ ' Looks for literal ">D1" ' CORRECT - Concatenate operator with cell =SUMIF(A:A, ">"&D1, B:B) ✓ \\\

Real-World Examples

Sales Analysis \\\ ' Total sales by region =SUMIF(RegionCol, "West", SalesCol) ' Sales by region and product =SUMIFS(SalesCol, RegionCol, "West", ProductCol, "Widget") ' Sales above target by salesperson =SUMIFS(SalesCol, SalespersonCol, "John", SalesCol, ">10000") \\\ Financial Reporting \\\ ' Sum expenses by category =SUMIF(CategoryCol, "Marketing", AmountCol) ' Sum expenses by category and department =SUMIFS(AmountCol, CategoryCol, "Marketing", DeptCol, "Sales") ' Sum expenses within budget period =SUMIFS(AmountCol, DateCol, ">="&BudgetStart, DateCol, "<="&BudgetEnd) \\\ Inventory Management \\\ ' Total stock by warehouse =SUMIF(WarehouseCol, "WH-001", QuantityCol) ' Low stock items (below reorder point) =SUMIFS(QuantityCol, QuantityCol, "<"&ReorderPoint, StatusCol, "Active") ' Stock value by category =SUMIFS(ValueCol, CategoryCol, "Electronics", StatusCol, "In Stock") \\\ HR and Payroll \\\ ' Total salary by department =SUMIF(DeptCol, "Engineering", SalaryCol) ' Overtime hours by employee and month =SUMIFS(OvertimeCol, EmployeeCol, "John Smith", MonthCol, "January") ' Bonus pool for high performers =SUMIFS(SalaryCol, RatingCol, ">=4", DeptCol, "Sales") * 0.1 \\\

Performance Tips

Use Specific Ranges \\\ ' SLOWER - Entire columns =SUMIF(A:A, "Apple", B:B) ' FASTER - Specific range =SUMIF(A1:A1000, "Apple", B1:B1000) \\\ Use Tables for Dynamic Ranges \\\ ' With Excel Table named "Sales" =SUMIF(Sales[Product], "Apple", Sales[Amount]) ' Automatically adjusts as data grows \\\ Consider SUMPRODUCT for Complex Logic \\\ ' When you need OR conditions or array operations =SUMPRODUCT((A1:A100="Apple")*(B1:B100>100)*(C1:C100)) \\\

SUMIF vs SUMIFS: Decision Guide

Use SUMIF when: You have only ONE condition You want simpler syntax You're working with older Excel versions Use SUMIFS when: You have TWO or MORE conditions You need date ranges (requires two conditions) You want consistent syntax across formulas Pro tip: Some Excel users always use SUMIFS even for single conditions, because: The syntax is consistent (sum_range always first) Easy to add more conditions later Works the same way as COUNTIFS, AVERAGEIFS, etc.

Related Functions

Function Purpose COUNTIF Count cells matching criteria COUNTIFS Count with multiple criteria AVERAGEIF Average with one criterion AVERAGEIFS Average with multiple criteria SUMPRODUCT Sum products of arrays

SUM vs SUMIF vs SUMIFS — Complete comparison COUNTIF vs COUNTIFS — Counting with conditions Excel Functions: Complete Guide Math & Trig Functions →

Formula debugging workflow

Step Action 1 Select the cell and read the formula in the formula bar 2 Use Formulas → Evaluate Formula for nested functions 3 Check for text stored as numbers (green triangle) 4 Fix errors using our formula error guide Go deeper: Compare similar functions · Fix common problems \nReporting: SUMIF · SUMIF vs SUMIFS · SUMIF not working \nReporting: SUMIFS function · Examples · SUMIF vs SUMIFS

Frequently asked questions

Why does my formula show an error? Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.
Should I copy formulas down or use a Table? Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.