Excel Sum Range Based on Cell Value: SUMIF and More

With the values to test in A2:A7, amounts to add in B2:B7, and the requested category in D2, use =SUMIF(A2:A7,D2,B2:B7) to sum only the amounts whose category matches D2. If D2 contains East and the East amounts are 120, 45, and 35, the result is 200.

The basic SUMIF pattern

SUMIF is the usual answer when a cell value determines which rows to include in a total. Its arguments are the range to check, the criterion to apply, and the range to add. The criteria range and sum range should cover the same number of rows, so each amount is paired with the correct category. They do not have to be in adjacent columns.

In the opening example, Excel checks each cell in A2:A7 against D2. When a row matches, it adds the value from the corresponding position in B2:B7. Changing D2 from East to West recalculates the result without changing the formula. This is useful for summary cells, reports, and dashboard controls where someone chooses a category from a cell.

The sum range is optional if the values being checked are also the values to add. With numbers in B2:B7 and a threshold in D2, the following formula adds only numbers at least as large as the threshold. If D2 is 50 and the values are 120, 80, 45, 70, 30, and 35, the result is 270.

With the numbers in B2:B7 and the minimum value in D2, use =SUMIF(B2:B7,">="&D2).

Using text, numbers, and comparison criteria

A criterion can be a text value, a number, a reference to a cell, or a comparison such as greater than or equal to a threshold. For an exact text match, point SUMIF at the cell containing the text rather than typing the text into the formula. This makes the criterion easy to change and reduces the chance of spelling the category differently in the formula.

Comparison operators are written as text. When the operator depends on a cell value, join the operator and cell reference with the ampersand. For example, if B2:B7 contains quantities and D2 contains the minimum quantity, this totals values greater than or equal to D2:

With quantities in B2:B7 and the threshold in D2, use =SUMIF(B2:B7,">="&D2,B2:B7).

For text that begins with a particular word, SUMIF supports the asterisk wildcard, which represents any number of characters. If product names are in A2:A7, amounts are in B2:B7, and D2 contains North, the formula below sums entries whose names begin with North. A question mark wildcard represents one character; use a tilde before a wildcard when you need to match the wildcard character itself rather than use it as a pattern.

With product names in A2:A7, amounts in B2:B7, and the starting text in D2, use =SUMIF(A2:A7,D2&"*",B2:B7).

When more than one condition matters

Use SUMIFS when a row must satisfy multiple conditions, such as matching both a category and a payment status. SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria. Each condition must be true for the amount on that row to be included; in other words, the conditions are combined with AND logic.

For a separate example, suppose A2:A7 contains regions, B2:B7 contains sales, and C2:C7 contains payment status. Put the desired region in D2 and the desired status in E2. This formula totals sales only for rows matching both selected values:

With regions in A2:A7, sales in B2:B7, statuses in C2:C7, the region in D2, and the status in E2, use =SUMIFS(B2:B7,A2:A7,D2,C2:C7,E2).

SUMIF is simpler for one condition; SUMIFS is generally clearer for two or more. Both can use comparison criteria, and SUMIFS can include several pairs of ranges and conditions. If you need OR logic, such as adding either East or West, you can add separate SUMIF results or use a more advanced approach. Check that the intended groups do not overlap, or the same record could be counted twice.

Choosing the sum column from a cell

Sometimes the criterion cell selects the rows, while a different cell selects which column to total. For example, a report may have a category in column A and monthly figures in columns B:D, with month names in B1:D1. One option is to use INDEX and MATCH to return the selected month’s column as the SUMIF sum range.

With categories in A2:A7, month headers in B1:D1, monthly values in B2:D7, the chosen category in F2, and the month name in G1, use =SUMIF(A2:A7,F2,INDEX(B2:D7,0,MATCH(G1,B1:D1,0))).

MATCH finds the position of the month named in G1; INDEX uses that position to supply the corresponding values from B2:D7. The row argument of zero asks INDEX for the whole selected column. The header text must match the value in G1, and the month headers should be unique. If G1 does not match a header, MATCH returns an error, so a dropdown list based on the headers can help prevent typing mistakes. INDEX and MATCH are available in older Excel versions as well as current Microsoft 365.

Making the end of a sum range depend on a cell

A different interpretation of “sum range based on a cell value” is that a number in a cell controls how many rows to include. For example, you might want to total the first three entries in a list, then change the count to five. INDEX can return the endpoint of a range for SUM to use.

With values in B2:B100 and the number of entries to include in D2, use =SUM(B2:INDEX(B2:B100,D2)). If D2 is 3, Excel sums B2:B4. If D2 is 5, it sums B2:B6. D2 must be a whole number from 1 through 99 for this particular range; zero or a number beyond the available list will not produce the intended result.

This approach differs from SUMIF: it selects a range by position, not by testing a category or other condition. If the list grows regularly, converting it to an Excel table can be a more maintainable option because table columns expand as rows are added. If a fixed count is specifically required, the INDEX endpoint keeps the count visible in a separate input cell.

How to choose the right approach

Choose the method that matches what the controlling cell represents:

A category, name, or other value to match: use SUMIF for one condition.
Several values that must all match: use SUMIFS.
A header that chooses which values column to add: combine SUMIF with INDEX and MATCH.
A count that sets how many consecutive entries to include: use SUM with an INDEX-defined endpoint.

For a straightforward category total, SUMIF is preferable to building a complicated formula. It is readable, recalculates when the criterion cell changes, and does not require a helper column. The more flexible approaches are useful when the structure really changes, but they also depend on valid headers, positions, or counts.

Common errors and ways to avoid them

If a result is zero when you expect a total, first check for mismatched text. Extra spaces, different spellings, and numbers stored as text can prevent an apparent match. Also confirm that the criteria range and sum range start and end on the same rows. SUMIF can return a result that looks plausible even when misaligned ranges pair criteria with the wrong amounts.

Other useful checks include:

Confirm that the criterion cell contains the value you expect, especially if it is driven by a dropdown or another formula.
For a comparison such as greater than a cell value, join the operator to the reference; do not put the reference inside quotation marks as if it were literal text.
For dates, make sure the cells contain real Excel dates rather than text that merely looks like a date.
In SUMIFS, verify the argument order: the sum range comes first, then each criteria range and its criterion.
If using the header-selection formula, check that the selected header exists exactly once in the header row.

In Microsoft 365 for Windows, formula argument separators are normally commas, as shown here. Some regional Excel settings use semicolons instead; if Excel rejects a correctly structured formula, check the list separator configured for your locale. On Mac, SUMIF, SUMIFS, INDEX, and MATCH work the same way; the formulas do not require a different version-specific method. Excel for the web also supports these functions.

Frequently Asked Questions

Can SUMIF use a cell reference as its criteria?
Yes. Put the desired value in a cell and use that cell as the criteria argument, as in the opening example. Excel then recalculates the total when the cell changes.

What is the difference between SUMIF and SUMIFS?
SUMIF is designed for one condition and lists the criteria range before the criterion. SUMIFS supports multiple conditions and lists the sum range first. Use SUMIFS when every condition must be satisfied on the same row.

Can I sum values when the cell contains part of a word?
Yes. Use the asterisk wildcard in the criterion for any number of characters, or a question mark for one character. A criterion joined to an input cell lets the partial text change without editing the formula.

Can one cell choose which column Excel sums?
Yes. Match the selected header against the header row, then use INDEX to return that column as the sum range. The header names need to match, and the selected name must exist in the header row.

Why does my SUMIF return zero?
Common causes include extra spaces or spelling differences in the criterion, numbers stored as text, a criterion that does not match, or ranges that do not line up. Check the underlying cell contents and the exact range boundaries.

Bottom line

For the common case—adding amounts for the category named in a cell—SUMIF is the direct solution: test the category range against the cell, then add the corresponding values. Use SUMIFS for additional conditions, INDEX and MATCH when a cell chooses the sum column, and an INDEX-defined endpoint when a numeric cell controls the number of rows. Matching the formula to the meaning of the input cell keeps the worksheet easier to check and maintain.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.