SUMIFS Function in Excel

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
↑Click any parameter above to learn what it means — it'll highlight in the live example below.
  • sum_range: (The numbers to add) The specific column or range of cells containing the numbers you actually want to add together. Required.
  • criteria_range1: (Where to look) The column or range of cells where Excel will search for your first condition. Required.
  • criteria1: (What to look for) The specific word, number, or condition Excel must find in the criteria_range1 before it adds the number. (See Common Criteria Patterns in SUMIFS below.) Required.
  • criteria_range2, criteria2: (Additional conditions) Extra columns and conditions you want Excel to check. First tell Excel where to look next (criteria_range2), then what to look for (criteria2). You can repeat this pattern up to 127 times. Optional.

What is the SUMIFS function in Excel?

The Excel SUMIFS function adds numbers together only if they meet one or more specific conditions that you set.

Think of a bouncer at a club. The bouncer only lets people inside if they meet specific conditions, like wearing a certain colour shirt or being on the guest list.

MathIntermediateWorks in Excel 2007+
!Golden Rules

SUMIFS's 3 golden rules

Reading these is a must. Skipping them is the #1 reason SUMIFS formulas break.

The SUMIFS function requires you to select the numbers you want to add before you select any conditions.
Every column or range of cells you select must start and end on the exact same row numbers.
If your sum_range is A1:A100, your criteria_range1 must also be exactly B1:B100.
If your condition is a specific word (like Completed) or uses mathematical symbols (like >50), you must put quotation marks around it.
If your condition is just a normal number (like 50), quotation marks are not necessary.

Learn with Example: SUMIFS

  1. Step 1: Understand the Goal We need to calculate the total Travel expenses for Jan first, and then automatically calculate them for Feb and Mar.
  2. Step 2: Start the Formula Click on the empty cell next to "Jan" in your Budget Summary table. Type: =SUMIFS(
  3. Step 3: Select sum_range For your sum_range, select the Amount column in the Expense Report table (D3:D7) because these are the actual numbers we want to add together. Then, type a comma. Type: =SUMIFS(D3:D7, sum_range The numbers you actually want to add.
  4. Step 4: Select criteria_range1 For your criteria_range1, select the Month column (A3:A7) because that is where Excel needs to look for our first condition. Then, type a comma. Type: =SUMIFS(D3:D7, A3:A7, criteria_range1 The column where Excel will search for your first condition.
  5. Step 5: Select criteria1 For your criteria1, select the cell containing the word "Jan" in your summary table (for example, "Jan", it is in cell A11). Then, type a comma. Type: =SUMIFS(D3:D7, A3:A7, A11, criteria1 The exact word or number Excel must find.
  6. Step 6: Select criteria_range2 For your criteria_range2, select the Expense Type column (C3:C7) because that is where Excel needs to look for our second condition. Then, type a comma. Type: =SUMIFS(D3:D7, A3:A7, A11, C3:C7, criteria_range2 The column where Excel will search for your second condition.
  7. Step 7: Type criteria2 For your criteria2, type "Travel" in quotation marks. Then, type a closing parenthesis ). Type: =SUMIFS(D3:D7, A3:A7, A11, C3:C7, "Travel") criteria2 The exact word or number Excel must find.
  8. Step 8: Check the First Result Press Enter. Excel perfectly adds the two January Travel expenses ($500 + $150) to give you $650! Result $650
  9. Step 9: Lock the Search Areas Before we can copy this formula down to Feb and Mar, we must lock our search areas so they don't slide down the page. Click on your formula, select D3:D7, A3:A7, and C3:C7, then press F4 on your keyboard to add $ signs. Change This =SUMIFS(D3:D7, A3:A7, A11, C3:C7, "Travel") To This =SUMIFS($D$3:$D$7, $A$3:$A$7, A11, $C$3:$C$7, "Travel") Hint: Do NOT lock the A11 cell! We want that cell to slide down to Feb and Mar when we drag the formula.
  10. Step 10: Copy Down and Check the Final Result Now, double-click the small green square in the bottom right corner of your cell. Excel will automatically fill in the correct travel totals for Feb and Mar! Result $650 $300 $0

You have a long list of company expenses spanning several months. How do you create a summary table that calculates the total amount spent on "Travel" for each individual month?

B11
ƒx
=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2])
ABCD
1Expense Report
2MonthDepartmentExpense TypeAmount
3JanSalesTravel$500
4JanITSoftware$200
5FebHRTravel$300
6JanMarketingTravel$150
7FebSalesMeals$100
8
9Budget Summary
10MonthTotal Travel Expenses
11Jan
12Feb
13Mar
14
Ready
Step-by-step guide1 / 10
Step 1: Understand the Goal

We need to calculate the total Travel expenses for Jan first, and then automatically calculate them for Feb and Mar.

When to use SUMIFS

Use the SUMIFS function when you need to add numbers based on specific conditions.

  • Sales reports: Add the total sales for a specific employee in a specific region.
  • Inventory tracking: Add the total number of items currently in stock that are marked as "Fragile".
  • Budget analysis: Add all expenses related to "Travel" from the month of "January".

When not to use SUMIFS

The SUMIFS formula is useful, but it is not the right choice for every situation:

  • When you do not have any conditions: If you just want to add a basic list of numbers, use the standard SUM function.
  • When you want to count items using multiple conditions: If you want to count items instead of adding them, use the COUNTIFS function. (For a single condition, use COUNTIF.)
  • When you want to find an average using multiple conditions: If you want to find the average of numbers instead of adding them, use the AVERAGEIFS function. (For a single condition, use AVERAGEIF.)

SUM vs SUMIF vs SUMIFS

If you are wondering whether to use SUM, SUMIF, or SUMIFS, this quick comparison table shows exactly which tool is best for your data.

FeatureSUMSUMIFSUMIFS
Best used forAdding everything togetherAdding based on one conditionAdding based on multiple conditions
Can use conditions?NoYes (limit: 1 condition)Yes (limit: 127 conditions)
Can check text words?NoYesYes
Can check dates?NoYesYes
Can check multiple columns?NoNoYes
Excel versionAll versionsAll versionsExcel 2007+
Ease of useBeginnerIntermediateIntermediate

Bottom line: Use SUM for everyday addition. If you need to add numbers based on conditions, use SUMIFS. It works for both single and multiple conditions.

Common Criteria Patterns in SUMIFS

The most common ways to set up your conditions when using SUMIFS. Each condition is added as a pair: a range, then what to check in it.

Criteria PatternFormula ExampleExplanation
Equal to text=SUMIFS(A2:A10, B2:B10, "Apple")Adds numbers in column A only if column B exactly matches the word Apple.
Greater than a number=SUMIFS(A2:A10, B2:B10, ">50")Adds numbers in column A only if the number in column B is greater than 50.
Not equal to=SUMIFS(A2:A10, B2:B10, "<>Completed")Adds numbers in column A as long as column B does not say Completed.

For every pattern, including how to use dates, cell references, and wildcards, see the full Excel Criteria and Wildcards reference.

Common Mistakes When Using SUMIFS

Getting a #VALUE! error because your sum_range and criteria_range are different sizes
Excel shows this error when your sum_range and your criteria_range do not match in size. For example, if your sum_range covers 10 rows (like A1:A10), your criteria_range1 must also cover exactly 10 rows (like B1:B10). If you select B1:B11 by mistake, the formula breaks.
Getting a 0 result because of a typo or missing quotation marks around your criteria
If your formula returns 0 incorrectly, Excel cannot find a match for your criteria. This happens if you forget to put quotation marks around text (for example, writing Apple instead of "Apple"), if there is a spelling error, or if the cells in your criteria_range have hidden spaces at the end of the words.
Getting an error because your sum_range is at the end instead of the beginning
Because the older SUMIF formula places the sum_range at the very end, many users mistakenly do the same with SUMIFS. You must always place your sum_range (the numbers you actually want to add) as the very first parameter inside the parentheses, followed immediately by your criteria_range1 and criteria1.
For a complete list of formula errors and how to fix them, see our Common Excel Errors Troubleshooting guide.

Frequently Asked Questions about SUMIFS

Quick answers about SUMIFS

Can I use SUMIFS with dates?

Yes. You can use SUMIFS to add numbers before, after, or between specific dates. You must put the date condition in quotation marks (for example, ">1/1/2024").

Does SUMIFS work with blank cells?

Yes. You can tell Excel to only add numbers if a specific cell is completely empty. Use "" as your condition.

What is the difference between SUMIF and SUMIFS?

SUMIF can only check one single condition. SUMIFS can check multiple conditions at the same time. Many users prefer to always use SUMIFS because it handles both situations perfectly.

Can I use wildcard characters?

Yes. You can use an asterisk (*) to represent any sequence of letters. For example, using "Apple*" as a condition will match "Apple", "Apples", and "Applesauce".

Is the SUMIFS formula case-sensitive?

No. Excel does not care about capital letters when using this formula. If your condition is "apple", Excel will successfully match and add numbers for "Apple", "APPLE", and "aPpLe".

Can I use a cell reference for my condition instead of typing a word?

Yes. Instead of typing "Apple", you can simply type a cell reference (like C1) that contains the word Apple. However, if you want to use a math symbol with a cell reference, you must use an ampersand (&). For example, to add numbers greater than the value in cell C1, type ">"&C1.

SUMIFS Practice Workbook

Download ready-to-use Excel files and practice SUMIFS hands-on

Complete Practice File
SUMIFS Practice Workbook
.xlsx · 74 KB
This file contains:
  • All 5 worked examples (finance, sales, HR, ops, data)
  • An answer key to check every total
Download Complete File
Reviewed by Grapte teamLast updated September 2026Tested in Microsoft Excel 365