Summing by Criteria With SUMIF

A regular SUM adds every number in a range. But often you only want to add some of them. Imagine a sales sheet where column A holds the region and column B holds the amount. You do not want the total of all sales, you want only the East region total.

This is a conditional sum, and the function built for it is SUMIF. It adds numbers only when a matching condition is met, skipping the rest automatically.

SUMIF takes three pieces in order:

• range the cells to test against your condition
• criteria the condition to look for
• sum_range the cells to actually add up

So the spreadsheet looks through range, finds rows that meet criteria, and totals the matching cells from sum_range.

Say region names are in A2:A10 and sale amounts are in B2:B10. To total just the East sales, test column A for the word East and add the matching amounts from column B.

The spreadsheet checks each region cell. Wherever it reads East, it grabs that row's amount and adds it to the running total. Other regions are ignored.

When your condition is a word, wrap it in double quotes: "East". Without quotes the spreadsheet would think East is a name or a label and likely return zero.

Matching is not case sensitive, so "east" and "East" match the same rows. Just be sure the spelling matches the data exactly, including no stray spaces.

Typing the condition by hand is fine, but it is cleaner to put it in a cell. If D1 contains the word East, reference D1 instead of writing the text.

Now you can change the region in D1 and your total updates instantly. This makes the formula reusable and turns one cell into a simple control switch for your report.

Criteria do not have to be text. To total every amount that equals exactly 500, just use the number as the condition.

When the test column and the sum column are the same, you can even leave out the third argument. Here column B is both tested and summed, so only the rows where B is 500 are added.

SUMIF can do more than exact matches. Put a comparison operator inside the quotes to add ranges of numbers:

• ">100" greater than 100
• "<=50" 50 or less
• "<>0" not equal to zero

This formula totals every sale larger than 100. The operator and the number both live inside one set of quotes.

What if the threshold lives in a cell, say D1 holds 100, and you want amounts greater than it? You cannot write ">D1" because that becomes the literal text D1. Instead join the operator and the cell with an ampersand.

The ampersand glues the ">" text to the value inside D1, building the criteria ">100" on the fly.

The range and the sum_range should be the same height and start on the same row. If region names sit in A2:A10, the amounts must be in B2:B10, not B3:B11.

When they are misaligned, the spreadsheet pairs the wrong region with the wrong amount and your total is silently wrong. Always double check that both ranges cover the exact same rows.

Picture an expenses sheet with categories in column A and costs in column B. You want a small summary table that shows the total for Travel, Food, and Office.

Put each category label in cells D2, D3, D4, then write one SUMIF that references the label cell. Fill it down and each row totals its own category. One formula pattern, three instant subtotals.

Notice the last example used A:A and B:B, whole columns. This is handy when rows keep getting added because you never have to extend the range.

Just make sure the header row text does not accidentally match your criteria, and keep both columns aligned. On very large sheets, a tight range like A2:A1000 can calculate a little faster.

Test your understanding of SUMIF argument order.

You now know how to add numbers conditionally with SUMIF. Key takeaways:

• The order is range, criteria, sum_range.
• Text criteria need quotes, and matching ignores case.
• Operators like ">100" live inside the quotes; join an operator to a cell with &.
• Keep range and sum_range aligned to the same rows.

Next you will count matching rows with COUNTIF.