Excel: Sum Values Based on Other Values

How-to basics · Updated November 18, 2026

Yes. In Excel, use SUMIF to add values that match one condition, or SUMIFS to add values that meet several conditions. With regions in A2:A8, amounts in C2:C8, and the criterion North in E2, enter =SUMIF(A2:A8,E2,C2:C8) to return 530.

How summing based on another value works

This task is a conditional sum: Excel checks one or more cells for a condition, then adds the corresponding values from a separate range. For example, a sales list might have a region in column A, a product in column B, and a sale amount in column C. You can total sales for one region, one product, or a combination without rearranging or filtering the list.

The key is that the condition range and sum range line up row by row. If A4 contains North and C4 contains 200, the 200 is included when North is the condition. Excel does not need the matching records to be grouped together; the function evaluates each row across the ranges.

For a small illustration, suppose the rows contain North/Tea/120, South/Coffee/80, North/Coffee/200, East/Tea/90, North/Tea/60, South/Tea/110, and North/Coffee/150. Summing the amount column for North gives 530. The same approach works with longer lists, provided the ranges cover the same rows.

Use SUMIF for one condition

SUMIF is usually the simplest choice when there is one test, such as a particular department, product, or status. Its argument order is condition range, condition, then sum range. The first range is where Excel looks for a match; the last is the set of numbers it adds.

With region names in A2:A8, amounts in C2:C8, and the text North entered in E2, use =SUMIF(A2:A8,E2,C2:C8). The result is 530. Referring to E2 rather than typing the condition into the formula makes it easy to change the requested region without editing the formula.

With the same ranges, you can type a condition directly into the formula: =SUMIF(A2:A8,"North",C2:C8). This also returns 530. Text criteria go in straight double quotation marks when written inside a formula. Cell references are not enclosed in quotation marks.

If the values to test are also the values to add, the sum range can be left out. For instance, with numbers in B2:B8 and a threshold stored in E2, =SUMIF(B2:B8,E2) adds the numbers in B2:B8 that equal E2. When the test and sum ranges differ, include the third argument so Excel knows which numbers to total.

Use SUMIFS for multiple conditions

Choose SUMIFS when a row must satisfy more than one condition. Its order differs from SUMIF: the sum range comes first, followed by pairs of condition ranges and criteria. Each pair adds another test, and all the tests must be satisfied for a row to count.

With regions in A2:A8, products in B2:B8, amounts in C2:C8, and the requested region and product in E2 and F2, respectively, enter =SUMIFS(C2:C8,A2:A8,E2,B2:B8,F2). If E2 contains North and F2 contains Coffee, the result is 350: the two matching amounts are 200 and 150.

For a fixed pair of conditions, with those same columns, use =SUMIFS(C2:C8,A2:A8,"North",B2:B8,"Coffee"). This also returns 350. Add further range-and-criterion pairs when the question calls for more tests—for example, region, product, and sales representative. Each criterion range must have the same dimensions as the sum range.

SUMIFS combines conditions with AND logic. A row must match every listed criterion. It does not mean “match this condition or that one”; for an OR request, use separate SUMIF calculations or a different formula approach.

Text, number, and wildcard criteria

Criteria can be exact text, a number, or a comparison. To express greater than, less than, or not equal to a value, put the operator and value together in quotation marks. With amounts in C2:C8 and a threshold of 100 in E2, =SUMIF(C2:C8,">"&E2) adds amounts greater than 100 and returns 680 for the example data. The ampersand joins the comparison operator to the value in E2.

For a fixed threshold of 100 in that same amount range, =SUMIF(C2:C8,">100") returns 680. This omits the sum range because Excel is testing and adding the same cells. Use ">=" for greater than or equal to, "<" for less than, and "<>" for not equal to.

Wildcards let a text condition match a pattern instead of an exact entry. An asterisk represents any number of characters, while a question mark represents one character. With product names in B2:B8 and amounts in C2:C8, =SUMIF(B2:B8,"Tea*",C2:C8) totals rows whose product name begins with Tea. A tilde can be used to search for a literal asterisk or question mark rather than treating it as a wildcard.

Sum values within a date range

Dates are often used as conditions to total sales for a month, quarter, or custom period. Excel stores real dates as serial values, so a date comparison works when the cells contain actual Excel dates rather than text that merely looks like a date. For a month, it is generally safer to test from the first day of that month up to, but not including, the first day of the next month.

With sale amounts in C2:C100, transaction dates in D2:D100, and actual Excel date values in D, this formula totals January 2026: =SUMIFS(C2:C100,D2:D100,">="&DATE(2026,1,1),D2:D100,"<"&DATE(2026,2,1)). The result is the sum of the amounts dated January 1 through January 31, inclusive. Using an exclusive February 1 boundary also avoids missing entries that include a time as well as a date.

To make the dates adjustable, put the first day of the month in E2 and the first day of the following month in F2. With the same amount and date ranges, use =SUMIFS(C2:C100,D2:D100,">="&E2,D2:D100,"<"&F2). Format E2 and F2 as dates for readability; the formulas depend on their underlying date values, not their display format.

Handle OR conditions and more complex logic

Because SUMIFS requires every condition to match, it is not by itself the right expression for “North or South.” If the categories are distinct and do not overlap, add two SUMIF results. With regions in A2:A8 and amounts in C2:C8, =SUMIF(A2:A8,"North",C2:C8)+SUMIF(A2:A8,"South",C2:C8) returns 720 for the example list: 530 for North plus 190 for South.

For a longer list of alternatives, you can add one SUMIF per alternative, or use a helper column to label rows that qualify and then sum by that label. Be cautious when conditions overlap. Adding separate totals for “starts with N” and “contains o,” for example, could count the same row more than once. Decide whether duplicates should count before choosing an OR formula.

When the requirement involves calculations across several columns or more complicated combinations of conditions, SUMPRODUCT may be an option. It can evaluate matching arrays and multiply corresponding values, but it is less approachable and easier to misconfigure than SUMIF or SUMIFS. For ordinary category totals, start with SUMIF or SUMIFS; use a more complex formula only when the simpler functions cannot express the rule clearly.

Choose the right method for the question

  • One condition: Use SUMIF, such as total amount by region.
  • Several conditions that must all match: Use SUMIFS, such as total by region and product.
  • One of several separate categories: Add individual SUMIF results, checking whether the conditions overlap.
  • Values need to be reviewed or rearranged: Apply a filter and inspect the visible rows, or create a PivotTable for a flexible summary by category.

Filtering is useful when you want to inspect the qualifying records, but a formula is better for a total that updates in a report as data changes. A PivotTable is useful for comparing many categories at once and exploring totals interactively. Neither replaces the formula in every case: SUMIF and SUMIFS are convenient when a report needs a specific result in a particular cell.

In Microsoft 365 for Windows, enter a formula in a worksheet cell and press Enter. The same SUMIF and SUMIFS formulas work in Excel for Mac and Excel for the web; ribbon placement can vary slightly by platform, but there is no different function syntax required for these examples. If the formula displays as text, check the cell format and whether an apostrophe was entered before the equals sign.

Common problems and how to fix them

  • Ranges are different sizes: In SUMIFS, the sum range and every condition range should cover corresponding rows. Using C2:C100 with a condition range A2:A99 can produce an error or an incorrect setup. Align the start and end rows.
  • Numbers are stored as text: A cell may look numeric but be left-aligned or imported as text. SUMIF may not add text-formatted values as expected. Convert the source values to real numbers, then check the total again.
  • Extra spaces or inconsistent labels: North and North followed by a space may not match as the same text. Clean imported data or standardize labels before relying on category totals.
  • Criteria quotation marks are wrong: Text typed into a formula needs straight double quotation marks. When the condition is in a cell, refer to that cell directly without quotes.
  • SUMIF and SUMIFS arguments are mixed up: SUMIF starts with the criteria range; SUMIFS starts with the sum range. Recheck the function name and argument order if the formula returns an implausible total.
  • Dates are text: A text date may not compare like a genuine Excel date. Convert the source dates to date values, then use the date-range test again.
  • Formula does not update as expected: Confirm calculation is set to Automatic under Formulas > Calculation Options. Also check that new data lies inside the ranges referenced by the formula.

For expanding lists, Excel Tables can make formulas easier to maintain because references can use column names and grow when rows are added. Convert a data range to a table with Insert > Table, confirm that the table has headers if it does, and use the generated column names in formulas. This is especially helpful for reports that receive new rows regularly; fixed ranges otherwise need to be extended when data is appended beyond their last row.

Frequently Asked Questions

Can Excel sum cells based on text in another column?
Yes. Use SUMIF for one text condition or SUMIFS for multiple conditions. For example, a region column can determine which corresponding amounts Excel adds.

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and places the criteria range first. SUMIFS handles multiple conditions and places the sum range first. In SUMIFS, all supplied conditions must be true for a row to be included.

Can I use a cell reference as the SUMIF condition?
Yes. Put the requested text or value in a cell and use that cell reference as the criterion, without quotation marks. This lets you change the requested result by changing the cell.

How do I sum values between two dates?
Use SUMIFS with one condition for dates on or after the starting date and another for dates before the next boundary. For a full month, use the first day of the month as the inclusive start and the first day of the next month as the exclusive end.

Why does my SUMIF formula return zero?
Check that the condition exactly matches the source data, that there are no extra spaces, and that the sum cells contain numbers rather than text. Also verify that the condition range and sum range cover matching rows and that text criteria use straight quotation marks.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.