OmniDocHow to Use SUMIF in Excel to Sum by Condition
Learn how the SUMIF Excel formula adds only the values that match one condition, with setup steps, a result check, and fixes for a total that shows zero.
If you keep sales, expenses, or inventory in a spreadsheet, you will eventually need a total for one group only: all sales from the East region, or all expenses tagged "travel." Scrolling and adding by hand works for a few rows, but it breaks down quickly and is easy to get wrong. This tutorial shows you how to use the SUMIF Excel formula to add up values that match one condition.
The short version: SUMIF checks a range of cells against a condition, then adds the matching values from a second range. You need three pieces, which are the range to test, the condition, and the range to add. The steps below use a simple sales table so you can reproduce each one.
You will also learn how SUMIF differs from IF and from lookup formulas, which are often confused with it. The last section covers what to check after you save the workbook, including how to open the same file in UniCell, the spreadsheet editor in the OmniDoc desktop client.
How does SUMIF work in Excel? The short answer
SUMIF adds the numbers in one range when the matching cells in another range meet a condition you set, so use it whenever the question is "what is the total for this group?"
| Your situation | What to do | Why |
|---|---|---|
| You need a total for one category | Use SUMIF with the category as the condition | It adds every row where the category matches |
| The condition may change | Put the condition in a cell and reference that cell | You update one cell instead of rewriting the formula |
| You need a label or flag on each row | Use IF | IF returns a value for each row; it does not add |
| You need one field from the matching row | Use a lookup function | It returns one value, not a sum of many |
| You need to match two or more conditions | Use SUMIFS, the version with extra condition pairs | SUMIF accepts only one condition |
Before you start
Check these points before you type the formula. Most zero totals and errors come from one of them.
- Use a header row and one table per sheet when you can. Headers make it easy to see what each column holds, and a clean block of rows keeps your ranges predictable.
- Confirm that the numbers are real numbers. A value that looks like 250 but is stored as text will not be added. Numbers that are text usually align to the left of the cell, while real numbers align to the right.
- Make the category spelling consistent. "East", "east ", and "East." are different entries to a condition. Fix the spelling in the source column before you build the formula.
- Decide which column holds the condition and which holds the amounts. Write this down. The formula needs both, and mixing them up gives a total that looks plausible but is wrong.
- Know the size of your data. You will reference a block such as rows 2 to 13. The two ranges in the formula must cover the same number of rows.
Writing a SUMIF formula in Excel, step by step
This example uses a 12-row sales list with the region in column A, the sales rep in column B, and the amount in column C, starting in row 2 (row 1 holds the headers). The goal is the total for the East region.
- Open the workbook and find an empty cell for the result. Pick a cell outside the data, such as E2, so the total does not sit inside the list you are summing.
- Start the formula with the function name. Click the cell and type
=SUMIF(. The opening parenthesis tells the spreadsheet that the arguments come next. - Enter the range to test. Type
A2:A13, then a comma. This is the range the spreadsheet checks against your condition. - Enter the condition. Type
"East"in quotes, then a comma. Quotes mark text. If you would rather change the region later, type a cell reference instead, such asG1, where G1 contains East. - Enter the range to add. Type
C2:C13and close the parenthesis. The formula should read=SUMIF(A2:A13,"East",C2:C13). Press Enter to see the total. - Lock the ranges before you copy the formula. If you plan to copy the formula to other regions, change the ranges to
$A$2:$A$13and$C$2:$C$13. The dollar signs keep the ranges fixed when the formula moves to another cell. - Copy the formula for other groups. Put the region names in a column, such as G2 to G4, and in the total column use a reference to the region cell, like
=SUMIF($A$2:$A$13,G2,$C$2:$C$13). Copy that formula down to get one total per region. - Save the workbook and confirm the result. Save the file in Excel format (.xlsx) so other spreadsheet editors can open it. You can open it in UniCell, the spreadsheet editor in the OmniDoc desktop client, and compare the total with the one you calculated by hand in the check below.
Check the result
Once you have a total, check it before you rely on it. Different problems produce different symptoms, so use this table to decide what to fix.
| Check | How to see it | If it's wrong |
|---|---|---|
| The total matches a manual check | Filter the list to East, then add the amounts yourself | Confirm both ranges cover the same rows and the condition spelling matches |
| The total is zero | Look at the cells in the amount column | Numbers stored as text need to be converted to numbers |
| The total is off by one row | Check whether the first or last row is missing from the range | Adjust the range to include every data row, such as row 13 |
| The total changed after you inserted rows | Click the formula and look at the range references | Insert rows inside the range, or reselect the range to include the new rows |
| A region with extra spaces is missing | Compare the category text in the cell with the condition | Trim the extra spaces in the source column and recalculate |
SUMIF does not distinguish uppercase from lowercase, so "east" and "East" count as the same condition. Spaces and punctuation do count, which is why a trailing space can drop a row from the total.
If you have many rows, add a second check: compare the sum of all groups with the grand total for the column. When the group totals add up to the grand total, you have not left a category out of the list.
🎯 Which approach fits you?
Readers usually land on SUMIF when they need a group total, but a few other formulas look similar. Use this to pick the right tool.
- Use SUMIF when you need a total. A single condition and a single sum range are enough for a regional total, a category total, or a monthly subtotal.
- Use IF when you need a result for each row. For example, you can label each row "Over budget" or "Within budget." IF does not add anything across rows.
- Use a lookup function when you need a value from one matching row. Suppose you want the sales rep for order number 1047. That is a lookup, not a sum.
- Use SUMIFS when you need more than one condition. For example, the East region in March. You add extra condition pairs to the same pattern.
If you are unsure, ask what you expect the answer to be. One number for a group means SUMIF or SUMIFS. A value per row means IF. A single field pulled from a matching row means a lookup.
❓ FAQ
1. Can SUMIF use more than one condition?
No, SUMIF accepts one condition. When you need to match two conditions, such as region and month, use SUMIFS, which takes the same arguments plus additional pairs of range and condition.
2. Why does my SUMIF return 0?
A common causes are numbers stored as text, a condition that does not match the spelling in the cells, or a range that points to the wrong column. Start with the check table above, then confirm that the condition appears exactly in the source data.
3. Is SUMIF case-sensitive?
No. The condition "east" matches "East" and "EAST." Extra spaces and punctuation still matter, so clean up the source column if the total is missing rows.
4. Can I use SUMIF on data from a PDF table?
Not directly. Export the table from the PDF first. The PDF editor in the OmniDoc desktop client can export to Excel, after which you can build the SUMIF formula on the exported data. Check the exported numbers against the original PDF before you total them.
📝 Summary
SUMIF is the right formula when you need the total for one group of rows, and it works well with clean headers, consistent category names, and ranges of equal size. Test the formula with a manual total before you rely on it, and save the workbook as .xlsx so you can open it in another spreadsheet editor and compare the results.
