Excel SUMIF Function
excel-sumif• Pick a category and watch SUMIF add only the amounts that match the condition. • Learn the criteria range, criteria and sum range by example.
💡 Change the category and the total recalculates automatically. It works the same in the downloaded Excel file.
How to read SUMIF
Find the rows in the criteria range that equal the criteria, then add up only their values in the sum range.
If you omit the sum range, the criteria range itself is summed. Criteria can be text, a number, or a comparison (＞, ＜, ＜＞).
Worth knowing alongside SUMIF
SUMIF adds up only the rows that meet a condition. You can total exactly what you need without splitting the table or applying a filter, which makes it a staple for totals by category or by person.
The criteria can be more than plain text or a number. ">1000" adds only values above 1000, and "<>Fruit" adds everything that is not Fruit. An asterisk acts as a wildcard, so "App*" catches anything starting with App.
For two or more conditions use SUMIFS. The one thing to watch is that its arguments run in the opposite order — the sum range comes first. The same idea gives you COUNTIF for counting and AVERAGEIF for averages.
Frequently asked questions
My SUMIF keeps returning 0.
Nine times out of ten the two ranges are misaligned. The criteria range and the sum range must have the same number of rows and start on the same row. Stray spaces in the criteria cell, or numbers stored as text, also stop the match, so check those too.
How do I combine a cell reference with a comparison operator in the criteria?
Join them with an ampersand, as in ">"&F2, keeping the operator inside quotation marks. Writing ">F2" compares against the literal text F2 and gives the wrong answer. Dates work the same way: ">="&DATE(2026,1,1).
The results go wrong when I copy the formula down.
The ranges shift with the formula. Lock the criteria range and the sum range as $B$2:$B$8 and leave only the criteria cell relative, and dragging down then evaluates each row against its own condition. Selecting a range and pressing F4 adds the dollar signs for you.
How do I sum only the items that contain a certain word?
Wrap the word in asterisks for a partial match, as in "*apple*". A question mark stands for exactly one character, so "A?01" catches codes that differ by a single character. To search for a literal asterisk or question mark, prefix it with a tilde: "~*".
Can SUMIF pull values from another sheet or another file?
Another sheet in the same file just needs the sheet name, as in Sheet2!$B$2:$B$100. A reference to a separate file only calculates while that file is open and errors out once it is closed, so it is safer to bring data you use often into one file.
How do I sum blank cells, or everything except one value?
Use "=" as the criteria to add only blank cells and "<>" to add only rows that contain something. To exclude one value, write it as "<>Fruit". Press Run above and you can watch, row by row, which rows the condition keeps and which it skips.