Excel Sum Based on Criteria with SUMIF and SUMIFS

Formulas & functions · Updated November 18, 2026

If categories are in A2:A7 and amounts are in B2:B7, use =SUMIF(A2:A7,"East",B2:B7) to add the amounts for East; if the matching amounts are 120, 85, and 95, the result is 300. Use SUMIF for one condition and SUMIFS when every one of several conditions must be met.

What “sum based on criteria” means

A criteria-based sum adds selected numbers rather than totaling every value in a range. For example, a sales list might contain region, salesperson, date, and revenue columns. You can total revenue for one region, one salesperson, a date period, or records that meet a combination of those conditions.

Excel’s main tools for this are SUMIF and SUMIFS. SUMIF tests one condition. SUMIFS tests multiple conditions and adds a value only when all of them match. Both are useful for summarizing ordinary ranges without first filtering or rearranging the source data. The result updates when the underlying values change, so the formula can serve as a small, live summary.

Think of the formula as having two jobs: check a range for a criterion, then add the values at the corresponding positions in a sum range. The checked cells and the amounts do not have to be in the same column, but their rows must line up. If a condition matches the third cell in the criteria range, Excel adds the third corresponding cell in the sum range.

How SUMIF works

SUMIF has three arguments: the range to check, the criterion to apply, and the range to add. The sum range is optional. If you leave it out, Excel adds the cells in the range it checked, which is useful when the numbers themselves determine whether they qualify.

With regions in A2:A7 and sales in B2:B7, this formula totals sales for East:

=SUMIF(A2:A7,"East",B2:B7)

If East appears in rows 2, 4, and 7, Excel adds B2, B4, and B7. It does not add the other sales values. In this example, if those three amounts are 120, 85, and 95, the result is 300. The range references can be changed to match your worksheet, but keep the criteria range and sum range the same size and aligned row by row.

You can also check and add one numeric range. With amounts in B2:B7, this formula totals values greater than 100:

=SUMIF(B2:B7,">100")

The condition is written in quotation marks because it combines a comparison operator with a value. When the criterion is a fixed word such as East, put that text in quotation marks too. A cell reference containing the criterion is entered differently, as the next sections explain.

Choose SUMIF or SUMIFS

Use SUMIF when a single test is enough, such as one product category or a minimum amount. Use SUMIFS when a row must satisfy two or more tests, such as a particular region and salesperson at the same time. In SUMIFS, the sum range comes first, followed by pairs of criteria ranges and criteria. That argument order differs from SUMIF, so it is worth checking when switching between them.

With regions in A2:A6, salesperson names in B2:B6, and revenue in C2:C6, this formula totals revenue for East handled by Ana:

=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Ana")

Suppose the rows contain East/Ana/120, East/Ben/80, West/Ana/150, and East/Ana/60, with any additional rows not matching both conditions. The result is 180: the formula adds 120 and 60. SUMIFS uses AND logic: a row must satisfy the East test and the Ana test. It does not mean East or Ana.

For several conditions, add another criteria-range-and-criterion pair. For example, a further test could restrict the qualifying rows to a date range or to amounts above a threshold. The ranges in a SUMIFS formula should have matching dimensions. A range that starts or ends on a different row can cause incorrect results or an error.

Use numbers, comparisons, and cell references as criteria

Criteria can test text, exact numbers, or comparisons such as greater than, less than, and not equal to. Comparison operators are part of the criterion. To use a number stored in another cell, join the operator to that cell reference with the ampersand (&).

With item names in A2:A20 and quantities in B2:B20, if D1 contains the minimum quantity, this formula adds quantities for rows whose item name is Apples:

=SUMIF(A2:A20,"Apples",B2:B20)

For a numeric threshold instead, with quantities in B2:B20 and the threshold in D1, this formula adds only quantities greater than the value in D1:

=SUMIF(B2:B20,">"&D1)

Without the ampersand, Excel would read the criterion as text rather than combining the greater-than operator with the cell’s value. Common operators include > for greater than, < for less than, >= for greater than or equal to, <= for less than or equal to, and <> for not equal to.

Criteria can also refer to text in a cell. With regions in A2:A20, sales in B2:B20, and the requested region in D1, this formula uses D1 as its criterion:

=SUMIF(A2:A20,D1,B2:B20)

This is handy for a summary sheet where someone can select or type a region without editing the formula. If the criterion cell is blank, the result may not represent the intended category, so consider labeling the input cell and checking that it contains a value.

Sum values for a date period

Dates can be criteria, but it is safer to build criteria from DATE than to type a date as text. Excel stores dates as serial numbers, and text-formatted dates can be interpreted differently depending on regional settings. To total a month, use a start date inclusive and the first day of the following month exclusive. This approach also works when the date cells include times.

With transaction dates in A2:A6 and amounts in B2:B6, this formula totals January 2026:

=SUMIFS(B2:B6,A2:A6,">="&DATE(2026,1,1),A2:A6,"<"&DATE(2026,2,1))

If the January amounts are 100, 150, and 50, and the other rows fall outside January, the result is 300. The inclusive lower bound includes January 1; the exclusive upper bound stops before February 1. Using a less-than test for the next month avoids missing entries that have a time attached on the last day of January.

For a month that changes, put its first day in D1 and calculate the first day of the next month with a date function, or build a reporting period using start and end date cells. Make sure the source dates are real Excel dates rather than text that only looks like dates; changing a cell’s display format alone does not convert text into a date value.

Use wildcards for partial text matches

When exact text is too restrictive, SUMIF and SUMIFS support wildcards. An asterisk (*) represents any number of characters, including none. A question mark (?) represents exactly one character. For example, a criterion beginning with North followed by any text can match North, Northern, or North Zone.

With product names in A2:A20 and sales in B2:B20, this formula totals products whose names start with “Desk”:

=SUMIF(A2:A20,"Desk*",B2:B20)

To match a literal asterisk or question mark in the cell text, precede the wildcard character with a tilde (~). For example, the tilde changes the wildcard into a character to search for. Wildcards are particularly useful for inconsistent labels, but they can also match more records than expected. If a result seems too large, check which entries begin with or contain the text in the criterion.

Sum for either of several categories

A frequent point of confusion is OR versus AND logic. SUMIFS requires every listed condition to be true for a row to count. If the requirement is to total East or West, use separate SUMIF calculations and add their results. This keeps the OR logic explicit and is easy to audit.

With regions in A2:A7 and amounts in B2:B7, this formula adds the East total and the West total:

=SUMIF(A2:A7,"East",B2:B7)+SUMIF(A2:A7,"West",B2:B7)

If East amounts are 120, 80, and 60 and West amounts are 150 and 75, the result is 485. The Central amount, if present, is left out. Avoid adding overlapping criteria without thinking through whether a row could be counted twice. For example, a broad wildcard and a specific category may both match the same text.

Common problems and ways to check a result

A zero or unexpectedly small total usually points to a mismatch between the criterion and the source data. Check spelling, extra spaces, number-versus-text values, and whether the right columns were selected. Text such as “100” may look numeric while being stored as text, which can affect comparisons and sums. Also confirm that the sum range is the column containing the amounts, not the column containing the labels.

  • Check range alignment. In SUMIF, the criteria range and sum range should cover corresponding rows. In SUMIFS, the sum range and every criteria range should have matching dimensions.
  • Inspect the actual values. A label may contain a trailing space or a slightly different spelling. Wildcards can help diagnose partial matches, but do not use them as a substitute for cleaning important source data.
  • Check the criterion syntax. Put text and comparison expressions in quotes. Join an operator to a cell reference with & when the threshold comes from a cell.
  • Verify dates and numbers are stored correctly. Formatting changes how a value appears; it does not necessarily change text into a number or date.
  • Test a small sample manually. Filter or inspect a few matching rows, add their amounts, and compare that total with the formula result.

SUMIF and SUMIFS are not case-sensitive for ordinary text criteria, so they do not distinguish East from EAST. If capitalization is meaningful, these functions alone are not a case-sensitive solution. Similarly, a formula can return zero because no cells meet the criterion, not because Excel failed to calculate. Check the matched records before changing calculation settings.

Make summaries easier to maintain

Fixed ranges such as A2:A100 work well for a stable list, but new rows added below the range will not be included automatically. One way to reduce that maintenance is to convert the data into an Excel table: select a cell in the data and choose Insert > Table. Tables expand as records are added and make formulas easier to read with column names. In Microsoft 365 for Windows, the table tools appear on the ribbon when the table is selected; the exact placement can vary with the ribbon layout.

For a small workbook, ordinary cell references are often clearer. For a growing transaction log, a table can make the source ranges more resilient and easier to understand. Whichever structure you choose, keep one header row, avoid mixing totals into the source records, and use consistent labels. SUMIF and SUMIFS are available in current desktop Excel and Excel for the web; the formulas described here use standard syntax.

Frequently Asked Questions

Can Excel sum with more than one condition?
Yes. Use SUMIFS and add one criteria range and criterion for each condition. A row contributes only when it meets all of those conditions. For alternatives such as East or West, add separate SUMIF results instead.

What is the difference between SUMIF and SUMIFS?
SUMIF is designed for one criterion and takes the criteria range first. SUMIFS supports multiple criteria and takes the sum range first. Remembering that argument-order difference helps prevent formulas from adding the wrong cells.

How do I sum values greater than a number in a cell?
Join the greater-than operator to the threshold cell with an ampersand. Put the combined criterion in quotation marks around the operator, as in the example above. If the threshold is fixed, you can include the number directly in the criterion.

Can SUMIF use dates as criteria?
Yes. Use a real Excel date value and comparison criteria. For a period, SUMIFS with an inclusive start date and an exclusive next-period date is usually dependable, including when date cells contain times.

Why does my SUMIF formula return zero?
Often the criterion does not exactly match the data, the amount range is wrong, or numbers and dates are stored as text. Check the source values and range sizes, and verify whether any rows actually meet the condition before assuming the formula is broken.

Related

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.