SUMIF in Excel: total a category without losing refunds

Keep purchases and returns in one consistent log

A community garden records five entries: Seeds 24, Tools 60, Seeds 18, Tools -12 and Compost 35. The negative Tools entry is a returned item that reduces the amount spent on tools. Enter categories in A2:A6 and signed amounts in B2:B6.

Put Tools in D2 as the category you want to total. The amounts use one common currency; there is no currency conversion or tax calculation in this exercise.

Select rows by category, then add their amounts

SUMIF first checks A2:A6 against the category in D2. It then adds the corresponding values from B2:B6. For Tools, the included amounts are 60 and -12, so the result is 48.

The return is not an exception to remove from the range. Its negative sign is how this log represents the reduction. Filtering only positive amounts would answer a different question: gross purchases before returns.

=SUMIF(A2:A6,D2,B2:B6)

Change the question without changing the formula

Replace Tools in D2 with Seeds. The total becomes 42, from 24 plus 18. Select Compost and the result is 35. Using a selector cell is easier to inspect than editing quoted text inside the formula each time.

You can also write a literal category as "Tools" inside the formula, but do not put quotation marks around D2 when you mean the cell reference. "D2" asks for a category whose text is literally D2.

A zero total does not prove there were no entries

Select Water, which does not occur in the initial log, and SUMIF returns 0. Now select Tools again and change the return in B5 to -60: the total is also 0, even though two entries matched.

A zero can therefore mean no matching rows, all zero-valued amounts, or positive and negative amounts that cancel. Inspect the rows or count the matching category before deciding which explanation applies. Do not label every zero total No purchases.

Protect the source ranges when copying a summary

Suppose D2, D3 and D4 will list Tools, Seeds and Compost. Lock the source ranges in the formula below before copying it down beside those labels. The D2 selector remains relative so the next summary row can use D3.

Keep both source ranges aligned. A category in A5 describes the amount in B5, not B4. When you extend the source log, include the new rows in both ranges. The practice panel evaluates a formula but does not simulate dragging its fill handle.

=SUMIF($A$2:$A$6,D2,$B$2:$B$6)

Move to SUMIFS when the question adds a condition

A category total needs one condition. A report asking for Tools purchased during a selected month also needs date conditions. That is a suitable next step for SUMIFS, with a separate date column.

The argument order changes: SUMIF places the amount range third, while SUMIFS places it first. Do not just add an S to the name and leave the arguments in the old order. Continue to the next lesson for a worked multi-condition report.

Check the sign with a deliberate edit

Reset the original data and select Tools. Change B5 from -12 to 12: the result rises from 48 to 72. The formula is still working; the edited sign has changed the meaning of that entry.

Then restore -12 and recategorize A5 as Seeds. Tools now totals 60 because the returned amount belongs to another category. These edits test both the category match and the signed arithmetic.

Further reading

• Microsoft: SUMIF function
• Microsoft: SUMIFS function and argument order