SUMIF and SUMIFS Explained Step by Step

Once you can add up only the rows that meet a condition, your bookkeeping spreadsheet stops being a list and starts being a report. That is what SUMIF and SUMIFS do. SUMIF adds up figures that match one condition; SUMIFS handles several at once. In this guide I will explain both step by step with examples a UK business will recognise, and by the end you will be building live summaries that update themselves as you add transactions.

These two functions are the quiet backbone of most bookkeeping spreadsheets. Whenever you see a tidy summary showing total sales, total travel, this month versus last month, there is almost certainly a SUMIF or SUMIFS doing the work underneath. They are not glamorous, but they are reliable, and once you understand the pattern you can answer almost any “how much did we spend on X” question with a single line of formula.

The data we will work with

Assume the categories sit in column B, customers in column C and amounts in column D.

SUMIF: adding up by one condition

SUMIF takes three parts: the range to test, the condition, and the range to add up. To total all the sales, you would write =SUMIF(B:B,"Sales",D:D). Excel checks column B for the word “Sales” and adds the matching amounts from column D. To total travel instead, swap “Sales” for “Travel”.

You can also point the condition at a cell rather than typing the word. If F1 contains the category you want, use =SUMIF(B:B,F1,D:D). Now changing F1 instantly re-totals, which is how you build a tidy summary table. Better still, list all your categories down one column and put the same formula next to each, referencing its own row. In one stroke you have a complete breakdown of spending by category that recalculates the moment you add a transaction.

Referencing whole columns like B:B and D:D is convenient and perfectly fine for most small sets of data. If your file ever grows to tens of thousands of rows and feels sluggish, you can tighten the ranges to the rows you actually use, or convert your data to an Excel Table and refer to it by name. For everyday bookkeeping, though, whole-column references keep your formulas short and save you from constantly re-pointing them as the list grows.

Building a live summary table

The real power of SUMIF appears when you stop writing one-off formulas and start building a summary that maintains itself. List your categories down a column, then put a SUMIF next to each that points back to your transaction list. Because each formula reads its category from the cell beside it, you can copy a single formula down the whole list and every category totals correctly. Add a grand total at the foot with a plain =SUM(...) of your category totals, and cross-check it against a direct sum of the whole amount column; if the two agree, you know every transaction has been captured in exactly one category. That reconciliation step, totals from two directions that must match, is one of the most reassuring habits you can build into any bookkeeping spreadsheet, because it quietly proves your figures hang together.

Using comparisons in SUMIF

The condition does not have to be exact text. You can use operators in quotes. To add up every amount over £500, use =SUMIF(D:D,">500"). Notice there are only two parts here, because the range you are testing and the range you are summing are the same column, so you can leave the third part off.

SUMIFS: adding up by several conditions

SUMIFS flips the order: the sum range comes first, then each condition as a pair. To total only Acme’s sales, you need two conditions, category equals Sales and customer equals Acme: =SUMIFS(D:D,B:B,"Sales",C:C,"Acme"). The amount range leads, then each test range is followed by its condition. You can keep adding pairs for more conditions.

That switch in argument order is the one thing that catches everybody out, so it is worth saying plainly. In SUMIF, the thing you add up comes last. In SUMIFS, the thing you add up comes first. The easy way to remember it is that SUMIFS has to put the sum range first because it does not know how many conditions will follow, so it gets that out of the way before the open-ended list of pairs begins. Say it to yourself once or twice while you write your first few and it will stick for good.

A practical monthly summary

SUMIFS shines for month-by-month reporting. With dates in column A, you can total April’s sales by adding two date conditions: =SUMIFS(D:D,A:A,">="&DATE(2026,4,1),A:A,"<="&DATE(2026,4,30)). The ampersand joins the operator to the date so Excel reads it correctly. Copy this across twelve cells, one per month, and you have an instant monthly breakdown that updates as you add transactions. It pairs perfectly with our small business bookkeeping template.

You can combine date and category in the same formula to answer sharper questions, such as "how much did we spend on travel in April". Just add the category as a third condition pair: =SUMIFS(D:D,A:A,">="&DATE(2026,4,1),A:A,"<="&DATE(2026,4,30),B:B,"Travel"). Build a small grid with months across the top and categories down the side, each cell carrying a formula like this, and you have a full management report that nobody has to update by hand. This is the kind of thing accountants charge for, and you can build it in an afternoon.

One last tip: SUMIFS also accepts wildcards in text conditions. Use an asterisk to match part of a word, so =SUMIFS(D:D,C:C,"Acme*") totals every customer whose name starts with Acme. It is a handy way to group related entries without restructuring your data, though it does rely on your naming being reasonably consistent in the first place.

Common mistakes

• Mixing up the argument order. SUMIF is range, condition, sum range. SUMIFS is sum range first, then condition pairs. They are not the same.
• Forgetting quotes around operators. Comparisons like ">500" must be in quotes, including the operator.
• Mismatched range sizes in SUMIFS. Every range must be the same height, or Excel returns a #VALUE! error.
• Hidden spaces in text. "Sales " with a trailing space will not match "Sales". Tidy your data first.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF adds up figures based on a single condition. SUMIFS lets you apply several conditions at once, such as category and customer and date, all in one formula.

Why is the argument order different between them?

It is just how Excel designed them. SUMIF puts the sum range last; SUMIFS puts it first so it can be followed by any number of condition pairs. It catches everyone out at least once.

Can I use SUMIFS with dates?

Yes. Use two conditions with >= and <= and the DATE() function joined with an ampersand, as shown above, to total a specific month or period.

Should I use SUMIFS or a pivot table?

Both work. SUMIFS is best when you want totals in a fixed layout that updates live; a pivot table is quicker for ad-hoc exploration. Many bookkeepers use both side by side.