How to Use SUMIF in Excel

Excel SUMIF adds up only the numbers that meet a condition, such as every Travel expense or every claim over 250. You write =SUMIF(range, criteria, sum_range): where to look, what to match, and what to add. This guide walks through the syntax, the criteria you'll use most, and the mistakes that make SUMIF return 0, using one small expense table you can copy.

What does SUMIF do in Excel?#

SUMIF checks each row against one condition and adds the numbers from the rows that match. It has three parts:

=SUMIF(range, criteria, [sum_range])

The square brackets mean sum_range is optional. SUMIF works in every version of Excel and is not case-sensitive.

The examples use this expense table in A1:D9:

How to use SUMIF in Excel: step by step#

To total by category, give SUMIF the Category column to check, the word to match, and the Amount column to add:

1. Click an empty cell, for example G2.
2. Type =SUMIF( and select C2:C9, the Category column.
3. Type a comma, then "Travel".
4. Type a comma and select D2:D9, the Amount column.
5. Close the bracket and press Enter.

=SUMIF(C2:C9,"Travel",D2:D9)

The result is 1,545: 420 + 310 + 540 + 275. SUMIF looked down column C, found four Travel rows, and added their amounts from column D.

When can you leave out sum_range?#

Leave out sum_range when the condition is about the same numbers you want to add. To total every claim over 250:

=SUMIF(D2:D9,">250")

SUMIF checks the amounts and adds the ones that pass: 420 + 299 + 310 + 540 + 275 = 1,844. With only two parts, the checked cells and the added cells are the same.

SUMIF criteria examples#

SUMIF uses the same criteria rules as COUNTIF: text and operators in double quotes, numbers and cells without. Here are the common forms on the expense data:

Write operators touching the value, with no space: ">250", not "> 250". Not equal to is <>. The * in "A*" is a wildcard that stands for any characters. You'll find more patterns in Excel wildcards in COUNTIF and SUMIF.

How to use a cell as the criteria#

Point the criteria at a cell so one formula fills a whole summary. Put Marketing, IT and Sales in F7:F9, then type this in G7:

=SUMIF($B$2:$B$9,F7,$D$2:$D$9)

Select G7:G9 and fill down (Windows: Ctrl+D; Mac: ⌘D). You get 625 for Marketing, 574 for IT and 912 for Sales.

The $ signs lock both ranges, so they stay put as you fill. F7 has none, so it moves to F8 and F9. To add the $ signs quickly, click a reference in the formula and press F4 (Windows) or ⌘T (Mac). To compare against a number in a cell, join the operator with &. If G1 holds 250, =SUMIF(D2:D9,">"&G1) gives the same 1,844 as before.

Always check a summary like this. Every claim belongs to one department, so the three totals should add up to the grand total:

=SUM(G7:G9)=SUM(D2:D9)

This shows TRUE when 625 + 574 + 912 equals 2,111. A FALSE means a department is missing or misspelled.

Why is SUMIF returning 0 or the wrong total?#

SUMIF returns 0 when no row matches, and a wrong total when the ranges don't line up. Check these:

1. Ranges in the wrong order. =SUMIF(D2:D9,"Travel",C2:C9) checks the amounts for the word Travel and finds nothing. The range to check comes first.
2. Ranges of different sizes. Make range and sum_range cover the same rows, like C2:C9 and D2:D9. If they start on different rows, SUMIF adds the wrong cells.
3. Spaces or typos. "Travel " with a trailing space won't match "Travel". Clean the column with TRIM.
4. Numbers stored as text. SUMIF skips text, so imported amounts may add up to 0. Convert them with VALUE.
5. Missing quotes. Travel without quotes gives #NAME?.

When to use SUMIFS instead#

Use SUMIFS when a row must meet two or more conditions, such as Sales and Travel. SUMIFS puts the sum range first:

=SUMIFS(D2:D9,B2:B9,"Sales",C2:C9,"Travel")

The result is 730 (Amir's two trips). SUMIFS also handles a range of numbers, using the same column twice. To add the claims from 100 to 300:

=SUMIFS(D2:D9,D2:D9,">=100",D2:D9,"<=300")

That gives 299 + 120 + 275 = 694. Dates work the same way, which is how you total a month: see how to sum by date range with SUMIFS.

Mixing up the argument order of SUMIF and SUMIFS is an easy slip to make under time pressure, for example in a job test. SUMIF vs SUMIFS compares them side by side, and COUNTIFS and SUMIFS with multiple conditions goes deeper.

The same pattern works for other summaries. AVERAGEIF gives the average of matching rows, and MAXIFS and MINIFS give the highest and lowest. If you're preparing for a sales or operations role, see the Excel skills sales operations jobs ask for. For a quick syntax reference, open the SUMIF function guide.

Practise it#

Practise SUMIF in a live spreadsheet: total Travel claims, add claims over 250, build a department summary with locked ranges, and check it with a TRUE/FALSE test. Each step tells you exactly what went wrong if your formula doesn't match.