Excel FormulasSUMIF function in Excel
SUMIF adds up the cells that meet one condition.
SUMIF answers questions of the form "what is the total for this?": sales for one region, expenses for one category, hours for one employee. It is one of the functions that turns a list into a report.
Syntax
=SUMIF(range, criteria, [sum_range])| Argument | What it is |
|---|---|
range | The cells to test against the condition. |
criteria | The condition, such as "North", ">1000" or a cell reference. |
sum_range | The cells to add. If left out, the tested cells themselves are added. |
Examples
Each result below is what Excel itself showed for the formula on the sample sheet.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Salesperson | Region | Product | Units | Amount |
| 2 | 05-Jan-2026 | Asha | North | Notebook | 20 | 1200 |
| 3 | 12-Jan-2026 | Ravi | South | Pen | 50 | 500 |
| 4 | 20-Jan-2026 | Meena | North | Bag | 4 | 3000 |
| 5 | 02-Feb-2026 | Asha | East | Pen | 30 | 300 |
| 6 | 14-Feb-2026 | Kabir | South | Notebook | 15 | 900 |
| 7 | 28-Feb-2026 | Ravi | North | Bag | 2 | 1500 |
| 8 | 03-Mar-2026 | Meena | East | Notebook | 40 | 2400 |
| 9 | 15-Mar-2026 | Asha | North | Bag | 3 | 2250 |
| 10 | 22-Mar-2026 | Kabir | South | Pen | 60 | 600 |
| 11 | 01-Apr-2026 | Ravi | East | Notebook | 25 | 1500 |
| 12 | 18-Apr-2026 | Meena | North | Pen | 80 | 800 |
| 13 | 06-May-2026 | Asha | South | Bag | 5 | 3750 |
=SUMIF(C2:C13,"North",F2:F13)Result: 8750Looks down the Region column for North and adds the Amount on those rows.
=SUMIF(B2:B13,"Asha",F2:F13)Result: 7500Total sales made by Asha.
=SUMIF(F2:F13,">1000")Result: 15600No third argument: adds the amounts that are themselves more than 1000.
=SUMIF(D2:D13,"<>Pen",F2:F13)Result: 16500Not equal: the total for every product except Pen.
=SUMIF(D2:D13,"*book*",F2:F13)Result: 6000A wildcard: products whose name contains "book".
=SUMIF(A2:A13,">="&DATE(2026,3,1),F2:F13)Result: 11300A date condition: sales on or after 1 March 2026. The sign in quotation marks is joined to the date with &.
How it works
SUMIF goes down the first range and tests each cell against the condition. For every cell that passes, it adds the cell on the same row of the sum range.
The two ranges should be the same size and start on the same row. If they do not line up, Excel still gives an answer, taken from the wrong rows, with no error. This is the mistake to watch for.
Writing the condition
| To match | Write | Example |
|---|---|---|
| text exactly | the text in quotation marks | "North" |
| a number exactly | the number | 500 |
| greater or less than | sign and number, together in quotation marks | ">1000" |
| not equal to | "<>" and the value | "<>Pen" |
| the value in a cell | the cell reference | H1 |
| a comparison with a cell | sign in quotation marks, &, the cell | ">"&H1 |
| part of a text | wildcards | "*book*" |
| empty cells | two quotation marks | "" |
| cells that are not empty | "<>" | "<>" |
The rule that trips people up: a comparison sign must be inside quotation marks, and a cell reference must be outside them. ">H1" looks for the text H1. ">"&H1 uses the number in the cell.
Text conditions ignore capital and small letters. "north" matches North.
Putting the condition in a cell
Typing "North" into the formula means editing the formula for each region. Put the region names in a column and point the formula at them: =SUMIF($C$2:$C$13,H2,$F$2:$F$13). Copy it down beside the list of regions and you have a summary table. The dollar signs keep the two ranges fixed while H2 moves.
Dates
Dates are numbers inside Excel, so comparisons work. Build the date with DATE, or point to a cell holding one. Typing a date as text inside the quotation marks depends on the computer's regional settings and may be read as month first, so avoid it. For a whole month you need two conditions, a start and an end, which is a job for SUMIFS.
Limits
- One condition only. For "North and Asha", use SUMIFS.
- "Either of two values" is not possible in one SUMIF. Add two together:
=SUMIF(C2:C13,"North",F2:F13)+SUMIF(C2:C13,"East",F2:F13). - The condition cannot be case sensitive.
Common mistakes
- Ranges of different lengths.
- Forgetting the quotation marks around
">1000". - A total of 0 because the amounts are stored as text.
In Google Sheets
SUMIF works the same way in Google Sheets.
Related functions
Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.