Excel Sum Rows Based on Cell Value: Formulas That Work

To total values in rows where a cell matches a condition, use Excel’s SUMIF function. With categories in A2:A6 and amounts in B2:B6, this formula totals the amounts for East: =SUMIF(A2:A6,"East",B2:B6); if the East amounts are 120, 80, and 150, the result is 350.

What “sum rows based on cell value” means

Usually, this question means adding numbers from one column only for the rows where a related cell meets a rule. For example, a sales list might have a region in column A and a sale amount in column B. You want the total for East, without manually selecting each East sale. In that case, the condition range is column A and the sum range is column B.

There is another possible meaning: adding values across one horizontal row when matching labels appear above them. Excel can handle that too; the ranges are simply arranged horizontally rather than vertically. Both patterns are covered below.

Use SUMIF for one condition

SUMIF checks a range for one criterion and adds the corresponding values from another range. Its argument order is condition range, criterion, then sum range. In the opening example, Excel checks A2:A6 for East and adds the value from the same row in B2:B6 each time it finds a match.

For a slightly larger example, assume A2:A6 contains East, West, East, South, and East, while B2:B6 contains 120, 90, 80, 50, and 150. With those values in place, this formula returns 350: =SUMIF(A2:A6,"East",B2:B6). The three matching amounts are 120 + 80 + 150.

The condition and sum ranges should cover corresponding rows. If you leave out the third argument, Excel sums the condition range itself, which is useful only when the cells being checked are also the numbers you want to add. For most category-and-amount tables, specify the sum range so the result is unambiguous.

Sum rows using more than one condition

Use SUMIFS when every included row must meet two or more conditions. Unlike SUMIF, SUMIFS puts the sum range first, followed by pairs of condition ranges and criteria. This difference in argument order is a frequent source of incorrect totals.

Assume A2:A6 contains regions, B2:B6 contains payment statuses, and C2:C6 contains amounts. If the rows are East/Paid/120, West/Paid/90, East/Pending/80, East/Paid/150, and South/Paid/50, this formula totals East sales marked Paid: =SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Paid"). The result is 270, from 120 + 150.

Each additional condition is another range-and-criterion pair. For instance, a report could require both a region and a status, or a salesperson and a product type. In a multi-condition formula, the ranges must have compatible dimensions: use corresponding rows and the same number of cells in each range.

Use a cell as the criterion

Typing a condition directly into a formula is convenient for a one-off total. For a reusable report, put the desired category in a separate cell and point the formula to it. That way, changing the selected category does not require editing the formula.

Assume A2:A100 contains regions, B2:B100 contains amounts, and D2 contains the region to total, such as East. Use: =SUMIF(A2:A100,D2,B2:B100). When D2 says East, the formula adds the corresponding values in B for rows where A equals East. If D2 changes to West, the total updates for West instead.

You can use a reference for one or more conditions in SUMIFS as well. Keep the sum range first, then give each condition range and its criterion cell in pairs. This is useful when a worksheet has dropdown selections or input cells for a small summary report.

Criteria for numbers, partial text, and dates

Criteria can test more than an exact text match. For a comparison such as greater than, put the operator in quotation marks and join it to a cell containing the threshold with &. Assume B2:B5 contains unit counts, C2:C5 contains amounts, and D2 contains 100. The formula below adds amounts only for rows with more than 100 units: =SUMIF(B2:B5,">"&D2,C2:C5). If the counts are 80, 120, 140, and 95, and the corresponding amounts are 40, 60, 70, and 30, the result is 130.

For text that contains a word or fragment, use asterisks as wildcards. An asterisk matches any number of characters. Assume A2:A4 contains Green Apple, Apple Juice, and Pineapple, and B2:B4 contains 10, 15, and 20. This formula returns 45 because all three labels contain the letters apple: =SUMIF(A2:A4,"*apple*",B2:B4). These standard text criteria are not case-sensitive. To match a literal asterisk or question mark in text, precede the wildcard character with a tilde.

Dates can be criteria too, provided the cells contain real Excel dates rather than text that merely looks like a date. A useful approach for a date period is to include the start date and exclude the first day after the period. Assume A2:A4 contains dates, C2:C4 contains amounts, E2 contains the first day of the month, and F2 contains the first day of the next month. Use: =SUMIFS(C2:C4,A2:A4,">="&E2,A2:A4,"<"&F2). If the dates in that month have amounts of 50 and 75, and the remaining row is outside the month with an amount of 25, the result is 125. The exclusive end boundary also handles date cells that include times.

Sum across a horizontal row

If the condition labels run across columns, use SUMIF with horizontal ranges. Assume B1:F1 contains status labels and B2:F2 contains the corresponding values. If the labels are Approved, Pending, Approved, Approved, and Pending, and the values are 10, 5, 30, 20, and 8, this formula totals the Approved values: =SUMIF(B1:F1,"Approved",B2:F2). The result is 60.

The key is that the criteria range and sum range must line up in the same order. Excel checks each label in row 1 and, for a match, adds the value directly below it in row 2. This is the horizontal equivalent of matching a category in one column and summing a neighboring column.

Choose between SUMIF, SUMIFS, and a PivotTable

For a single condition, SUMIF is usually the clearest choice. For two or more conditions, use SUMIFS. Both are useful for totals that need to remain visible in a worksheet and recalculate when source values change. They are available in current desktop Excel versions and do not require a Microsoft 365 subscription.

A PivotTable can be a better fit when you want to explore many categories, rearrange the summary, or compare several measures without writing a separate formula for every group. Select the source data and choose Insert > PivotTable. Place the category field in Rows and the numeric field in Values; Excel typically summarizes numeric values by sum. PivotTables are interactive summaries, while a formula is often more convenient when a report needs a specific total in a fixed cell. After changing source data, refresh a PivotTable to update its summary.

Common formula problems and checks

If a result is zero or unexpectedly small, check the data and ranges before rewriting the formula. These are common causes:

Ranges do not align. The criteria and sum ranges should refer to corresponding records. Inconsistent starting or ending rows can pair the wrong amounts with labels.
Numbers are stored as text. A cell may look numeric but behave as text, especially after data is imported. Convert the source values to numbers before relying on a numeric total.
Text differs from the criterion. Extra spaces, spelling differences, or a category label that is not an exact match can prevent a row from being included. A wildcard can help when a partial match is intended, but should not be used to hide inconsistent data.
Dates are text. Date comparisons work best when the source cells and boundary cells contain Excel date values. Text dates may not compare as expected.
Wrong argument order. Remember that SUMIF starts with the condition range, whereas SUMIFS starts with the sum range.

Also note that SUMIF and SUMIFS include matching rows even if those rows are hidden or removed from view by a filter. A filtered display does not change what these formulas calculate.

When only visible matching rows should count

If the requirement is to total matching records that remain visible after filtering, a helper column can combine a condition check with a visibility check. Assume A2:A100 contains a nonblank record identifier in every data row, B2:B100 contains the category, C2:C100 contains the amount, and D is a helper column. In D2, enter this formula and fill it down: =IF(SUBTOTAL(103,A2)=1,IF(B2="East",C2,0),0). It returns the amount for a visible East row and zero for other rows. Then, with the helper results in D2:D100, use: =SUBTOTAL(109,D2:D100). This totals the helper values while excluding rows hidden by a filter or manually hidden.

Make sure the identifier cell used by the visibility check is populated for every record; otherwise, a visible row with a blank identifier may be treated as not visible by this test. If you do not need the total to respond to filtering, the simpler SUMIF or SUMIFS formula is easier to maintain.

Frequently Asked Questions

Can Excel sum a row when another cell equals a specific word?
Yes. Use SUMIF for one matching condition, with the range containing the words as the criteria range and the corresponding numeric cells as the sum range. Use SUMIFS when the row must meet multiple conditions.

How do I sum rows that meet a greater-than condition?
Set the criterion to an operator such as greater than or less than. If the cutoff is stored in a cell, join the operator text to that cell reference with &. Confirm that the values being compared are numbers, not text.

Does SUMIF distinguish uppercase from lowercase?
No. Standard SUMIF and SUMIFS text comparisons are not case-sensitive. If capitalization must be treated as meaningful, a different, case-sensitive approach is needed.

Can SUMIF add only the rows currently visible after filtering?
No. It includes matching rows even when they are hidden. For a filter-sensitive result, use a helper column with a visibility check and sum that helper column with SUBTOTAL, as described above.

Why does SUMIF return zero when the category appears to match?
Check for extra spaces or inconsistent labels, confirm that the criterion range is correct, and verify that the intended amounts are numeric. If the source category is a longer phrase containing the criterion, a wildcard match may be appropriate.

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.