How to Use the SUMIF Function in Excel (With Examples)

To use SUMIF in Excel, click an empty cell and type =SUMIF(range, criteria, sum_range): the range is the column Excel checks, the criteria is the condition in quotation marks, and the sum_range is the column of numbers to add. For example, =SUMIF(A2:A8,"East",C2:C8) adds every sales figure in C2:C8 whose region in column A is East. Press Enter and the total appears, and it updates whenever the data changes.

Applies to: Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel 2019, Excel 2016 and Excel for the web. The function works the same way in all of them, as described on Microsoft’s SUMIF function page.

SUMIF syntax and terms

=SUMIF(range, criteria, [sum_range])

• range (required) is the block of cells Excel tests against your condition, such as a Region or Category column.
• criteria (required) is the condition. It can be a number (950), text ("East"), a comparison (">1000"), a pattern with wildcards ("*st*"), a cell reference (F1) or a function such as TODAY().
• sum_range (optional) is the block of numbers to add. Leave it out and Excel adds the cells in range itself, which is handy when the condition and the numbers are in the same column.

Two rules cover most mistakes. First, text and anything with a comparison symbol (>, <, <>, =) goes inside double quotation marks; a plain number or a cell reference doesn’t. Second, SUMIF handles exactly one condition. For two or more, use SUMIFS (covered below).

The example data

Every formula in this guide uses this small sales list in A1:D8. Type it into a blank sheet if you want to follow along and check your answers.

Row	A (Region)	B (Rep)	C (Sales)	D (Date)
2	East	Alex	1200	1/15/2026
3	West	Jordan	950	2/3/2026
4	East	Priya	700	2/20/2026
5	North	Sam	1500	3/2/2026
6	East	Morgan	400	3/18/2026
7	(blank)	Casey	300	3/25/2026
8	West	Riley	850	1/28/2026

Row 7 has no region on purpose, so you can see how SUMIF treats empty cells. All seven sales add up to 5,900.

How to use SUMIF in Excel, step by step

This adds up all sales for the East region.

1. Click the cell for the result. Choose an empty cell, such as F2. The total will appear here.
2. Start the formula. Type =SUMIF(. Excel shows a small tooltip under the cell listing range, criteria and [sum_range], with the argument you’re on in bold.
3. Select the range to check. Drag over A2:A8, the Region cells, then type a comma. Don’t include the header in row 1.
4. Type the condition. Type "East", including the quotation marks, then a comma. Capital letters don’t matter here; "east" gives the same result.
5. Select the numbers to add. Drag over C2:C8, the Sales cells. Make sure this block starts on the same row as the range in step 3 and is the same height.
6. Finish the formula. Type ) and press Enter. The full formula is =SUMIF(A2:A8,"East",C2:C8), and the cell shows 2300 (1200 + 700 + 400).

To confirm the formula picked the right rows, click the result cell and look at the formula bar, or select C2, C4 and C6 while holding Ctrl (Command on a Mac) and check the Sum on the status bar at the bottom of the window. It should match.

Use the Function Arguments box instead of typing

If you’d rather fill in boxes than remember the argument order, click the result cell, then select Formulas > Math & Trig > SUMIF. In the Function Arguments box, click in Range and select A2:A8, type "East" in Criteria, click in Sum_range and select C2:C8. The box previews the result (2300) before you select OK. This route is handy while you’re learning, because each box explains what it expects. The layout of this pane can differ in Excel for Mac and Excel for the web.

Put the condition in a cell

Hard-typed conditions mean editing the formula every time you want a different total. Instead, type East in F1 and use:

=SUMIF(A2:A8,F1,C2:C8)

Change F1 to West and the result switches to 1800. Notice that a cell reference on its own has no quotation marks. If you want a comparison against a cell, keep the operator in quotes and join it to the cell with an ampersand (&):

=SUMIF(C2:C8,">"&F1)

With 1000 in F1, that adds every sale above 1,000 and returns 2700. Writing ">F1" instead compares against the literal text “F1”, which matches nothing useful.

If you plan to copy the formula to other cells, lock the data ranges with dollar signs ($A$2:$A$8), which absolute references in Excel formulas explains in more detail. Another option is to name the range in Excel first, so a formula like =SUMIF(Region,"West",Sales) stays readable and never shifts when copied.

SUMIF criteria examples

Each result below uses the example data, and you can paste any formula into an empty cell to check it.

What you want to add	Formula	Result
Sales over 1,000	=SUMIF(C2:C8,">1000")	2700
Sales of 1,000 or less	=SUMIF(C2:C8,"<=1000")	3200
Sales of exactly 950	=SUMIF(C2:C8,950)	950
Every region except East (includes the blank row)	=SUMIF(A2:A8,"<>East",C2:C8)	3600
Regions containing “st” (East and West)	=SUMIF(A2:A8,"*st*",C2:C8)	4100
Reps whose name ends in “y”	=SUMIF(B2:B8,"*y",C2:C8)	1150
Reps with exactly five letters in their name	=SUMIF(B2:B8,"?????",C2:C8)	1850
Rows where Region is empty	=SUMIF(A2:A8,"",C2:C8)	300
Rows where Region is filled in	=SUMIF(A2:A8,"<>",C2:C8)	5600
Sales at or above the third-highest sale	=SUMIF(C2:C8,">="&LARGE(C2:C8,3))	3650

A few details make these work:

• Wildcards. An asterisk (*) stands for any number of characters, including none, and a question mark (?) stands for exactly one character. They only work on text, so "1*" won’t find numbers that start with 1.
• Literal * or ?. To match a real asterisk or question mark, put a tilde in front of it, as in "~*" or "Item~?".
• “Not equal” includes blanks. "<>East" adds the empty Region row too, which is why its result (3600) is 300 more than West plus North.
• Search box. To total every row whose rep name contains whatever is typed in F1, wrap the cell in wildcards: =SUMIF(B2:B8,"*"&F1&"*",C2:C8).

Sum by date

Excel stores dates as numbers, so date conditions use the same comparison operators. The safest way to write a fixed date is with the DATE function, because a typed date like “2/1/2026” is read differently depending on your regional settings (February 1 in the US, January 2 in much of the world).

• Sales on or after February 1, 2026: =SUMIF(D2:D8,">="&DATE(2026,2,1),C2:C8) returns 3850.
• Sales dated today: =SUMIF(D2:D8,TODAY(),C2:C8).
• Sales before today: =SUMIF(D2:D8,"<"&TODAY(),C2:C8).

A date range (start and end) is two conditions on the same column, so it needs SUMIFS. For all of March 2026:

=SUMIFS(C2:C8,D2:D8,">="&DATE(2026,3,1),D2:D8,"<="&EOMONTH(DATE(2026,3,1),0))

That returns 2200 (1500 + 400 + 300). EOMONTH returns the last day of the month, so you don’t have to remember whether it has 30 or 31 days. To label each row with its month before totaling, use the formulas for converting a date to a month in Excel. If your dates have times attached, a “same day” match like TODAY() can miss them; use a start and end condition instead.

Build a total for every category

SUMIF is a quick way to make a small report with one total per region, product or expense type.

1. List each category once in a spare column, for example East, West and North in H2:H4. In Excel for Microsoft 365, Excel 2021 and later, =UNIQUE(A2:A8) can build the list for you (it will also list the empty region as 0).
2. In I2, enter =SUMIF($A$2:$A$8,H2,$C$2:$C$8). The dollar signs keep the data ranges fixed while H2 changes on each row.
3. Drag the fill handle in the bottom-right corner of I2 down to I4. You should see 2300, 1800 and 1500.

If you convert the data to a table first (Ctrl + T), use the column names instead, such as =SUMIF(Table1[Region],H2,Table1[Sales]), and new rows are included automatically. For a bigger report that builds the list and totals in one formula, GROUPBY in Excel is an option in Microsoft 365. This kind of category summary is also how a monthly budget in Excel fills in actual spending per category.

Add up rows that meet more than one condition

SUMIF tests one condition. When several conditions must all be true (AND logic), use SUMIFS. Its arguments come in a different order: the numbers to add go first, followed by pairs of range and condition.

=SUMIFS(C2:C8,A2:A8,"East",C2:C8,">500")

That adds East sales over 500 and returns 1900 (1200 + 700). Microsoft’s SUMIFS function page notes it accepts up to 127 range and condition pairs and that every criteria range must be the same size as the sum range. Mixing up the argument order when you switch between SUMIF and SUMIFS is one of the most common causes of wrong totals.

For “either/or” logic (OR), add SUMIF results together:

• =SUMIF(A2:A8,"East",C2:C8)+SUMIF(A2:A8,"West",C2:C8) returns 4100.
• The shorter version =SUM(SUMIF(A2:A8,{"East","West"},C2:C8)) gives the same answer.

Be careful with OR across two different columns (for example, East region or sales over 1,000). Adding two SUMIFs counts a row that meets both conditions twice. Subtract the overlap with SUMIFS, or use a helper column that marks each qualifying row once.

Troubleshooting SUMIF

The result is 0

Nothing matched. Check for extra spaces before or after the text in your data ("East " isn’t "East"), a typo in the condition, or missing quotation marks around text. Also look for numbers stored as text: they usually sit on the left of the cell, often with a small green triangle. SUMIF skips text in the sum range, so convert those cells to numbers (select them, then the warning icon, then Convert to Number).

The total is wrong, but there’s no error

The range and sum_range probably don’t line up. Excel doesn’t warn you: it starts at the first cell of sum_range and uses a block the same size as range. So =SUMIF(A2:A8,"East",C3:C9) is shifted down one row and returns 2750 instead of 2300. Make both ranges start on the same row and cover the same number of cells.

The operator is ignored

The comparison symbol has to be inside quotes, and a cell or function has to be joined to it with &, as in ">"&F1 or ">="&DATE(2026,2,1). Putting the whole thing in quotes, like ">F1", turns it into text.

#VALUE! error

According to Microsoft’s page on correcting a #VALUE! error in SUMIF and SUMIFS, the usual causes are a formula that points to another workbook that’s closed (open that workbook and press F9) and, in SUMIFS, ranges of different sizes. SUMIF also returns incorrect results when you try to match text longer than 255 characters; match a shorter, unique part with wildcards instead.

SUMIF won’t total across several sheets

SUMIF doesn’t support 3D references like Jan:Mar!A2:A8. Write one SUMIF per sheet and add them, as shown in adding totals from different sheets in Excel.

Excel won’t accept the formula

The range and sum_range arguments generally need to be real cell ranges. If you pass the result of another calculation there (for example, an array built by another function), Excel may reject the formula. In that case, use SUMPRODUCT or SUM with FILTER instead.

Frequently asked questions

What does SUMIF return if no cells match?

It returns 0, not an error. If you expected a total, that 0 is your cue to check spacing, spelling and quotation marks in the condition.

Is SUMIF case-sensitive?

No. “east”, “East” and “EAST” all match the same cells. For a case-sensitive total, use =SUMPRODUCT(--EXACT(A2:A8,"East"),C2:C8), which only counts exact capitalization.

What’s the difference between SUMIF, COUNTIF and AVERAGEIF?

They share the same kind of condition but return different answers: SUMIF adds the matching numbers, COUNTIF in Excel counts the matching rows, and AVERAGEIF averages them, as covered in calculating an average in Excel. Once you can write one, you can write the others.

Does SUMIF include hidden or filtered rows?

Yes. SUMIF adds every matching row in the range, whether or not it’s hidden or filtered out. If you only want visible rows, use the SUBTOTAL or AGGREGATE function on the filtered list instead.

How do blank cells and TRUE/FALSE values behave?

Empty cells in the sum range add nothing, and empty cells in the criteria range only match "" (or a “not equal” condition). Microsoft notes that in SUMIFS, TRUE counts as 1 and FALSE as 0 in the sum range, which can throw a total off if a column mixes check values with numbers.

Does SUMIF work in Google Sheets?

Yes. Google Sheets uses the same SUMIF(range, criterion, [sum_range]) layout and the same * and ? wildcards, as shown on Google’s SUMIF help page, so the formulas in this guide carry over.

Next step

Try one formula from the criteria table on your own data, then turn it into a cell-driven version so you can change the condition without editing the formula. If you’re still getting used to formulas, start with adding numbers in Excel and come back to SUMIF once plain SUM feels comfortable.