How to Use SUMIF in Excel (With Step-by-Step Example)

QuickBooks Online has a good array of standard reports, and those reports can be customized to produce some amazing data. However, sometimes a client wants to track data that doesn’t exist in QBO. For example, some of our clients use software outside of QBO to bill their services. In those cases, many small business owners turn to Excel to track payroll, expenses, or sales. But adding numbers manually (or filtering and summing repeatedly) takes time. That’s where the SUMIF function shines. With just one formula, you can quickly total amounts that meet a certain condition, like all expenses for the “Supplies” category or payroll for a specific location.

In this article, we’ll show you how to use SUMIF in Excel with a clear example, including formulas, screenshots, and tips to avoid common mistakes.

When to Use SUMIF (and Why It Matters for Small Businesses)

SUMIF is perfect when you want totals based on a single condition. A few examples:

• Total payroll by employee

• Sum expenses by category (e.g., rent, supplies, utilities)

• Track sales for a specific product or location

For small businesses, learning how to use SUMIF in Excel can:

• Save time on repetitive tasks

• Track spending or revenue by category or location instantly

• Reduce errors from manual calculations

This makes SUMIF especially useful for small businesses that need quick insights without creating complex pivot tables.

The SUMIF Formula Explained

The structure looks like this:

=SUMIF(range, criteria, [sum_range])

• range: The cells you want to check for your condition

• criteria: What you’re looking for

• sum_range (optional): The cells to add up. If omitted, Excel sums the same cells in the range.

Think of it as: “If this cell meets the condition, add the matching number from here.”

Example: Total by Location

Let’s assume a client wants to know the gross revenue for each of their 4 locations. Their software provides a downloadable Excel spreadsheet which includes the date, location, employee, and amount. While it would be easy to pull out a calculator for the one-week span shown in our example, this wouldn’t be as simple to compute for a longer time period, such as a whole quarter. That’s where the SUMIF formula can come to the rescue.

Example Dataset

Here is a simple way to use SUMIF to calculate the revenue by location.

First, list the locations you want to total, such as Ennis, Fort Worth, Grand Prairie, and Highland Park. Here’s an example of how our spreadsheet looks before entering the formula:

The formula needs to identify three things: the range of data to search, what to look for (the criteria), and the range of the data to add.

General structure:

=SUMIF(range, criteria, sum_range)

In our example, the range to search is in column B, specifically cells B2:B8. The first criterion is “Ennis.” The data to add up is in column D, cells D2:D8. So the formula is:

=SUMIF(B2:B8,"Ennis",D2:D8)

Excel will return the total revenue for all rows where the Location column equals Ennis.

Now that you understand how the formula works, replace the word “Ennis” in the formula with the cell location B12 (the cell where the word “Ennis” is).

=SUMIF(B2:B8,B12,D2:D8)

And finally, to make the ranges static (so the ranges do not change once we paste the formula), add “$” before each row #.  Do this in the ranges only – not the criteria.

=SUMIF(B$2:B$8,B12,D$2:D$8)

When you copy the formula to the cell below it, the criteria will move down one row to B13, but the ranges will stay in place.  You won’t have to type the location names into each formula.  This saves time and reduces the possibility of errors.

Once the formula is copied to the remaining locations (Fort Worth, Grand Prairie, Highland Park), the spreadsheet will look like this:

This approach makes it easy to calculate totals for multiple locations without manual addition.  A thousand rows of data can be calculated just as easily.

Pro tip: Double-check your data to make sure the totals match!

Need some more practice?

Want to try a bit more on your own? Try to replicate this formula searching for the total by employee for the week. Your results should be:

Additional Tips and Common Mistakes

• Text criteria must be in quotes: "Supplies"  (or better still – use the cell reference)

• Ensure the range and sum_range are the same size

• Watch out for extra spaces or misspellings in your list or even in the original data (they’ll break the formula if things don’t match exactly)

Wrapping Up: How to Use SUMIF in Excel

SUMIF is a powerful but easy-to-learn tool in Excel. By mastering it, you can spend less time crunching numbers and more time making decisions with your data.

At Overman Bookkeeping, we love tools like this that make financial data clearer. If you need help setting up financial systems or using Excel effectively for your business, reach out to us today. We’d be glad to help.