Excel SUMIFS: Multiple Criteria in the Same Column?

Formulas & functions · Updated November 19, 2026

Yes. In Excel, use SUMIFS with the same criteria range more than once when every condition must be true, or add separate SUMIFS results when any one of several values can match. For example, to total sales for either North or West, sum the two regional results; to total values between two limits, repeat the same criteria range with a lower and an upper condition.

What “multiple criteria in the same column” means

SUMIFS adds values from a sum range when the corresponding rows meet one or more tests. Its basic structure is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Each criteria range is paired with a criterion, and Excel checks the pairs for each row.

When you supply different criteria ranges, the tests normally combine as AND: the row must satisfy all of them. If two criteria pairs use the same range, both tests still apply to that one column. This is useful for numeric intervals, dates within a period, or text that must satisfy two compatible conditions.

A different situation is asking for one value OR another in the same column—for example, North or West. That is not the same as requiring a cell to equal North and equal West at once. Decide whether your conditions mean “all of these” or “any of these” before writing the formula; that distinction determines the formula pattern.

Use SUMIFS for “this value or that value”

For an OR condition, a straightforward method is to calculate one subtotal for each allowed value and add the results. Suppose A2:A7 contains regions and C2:C7 contains sales: North has 100 and 75, West has 200 and 25, South has 50, and East has 40. The total for North or West is 400.

With regions in A2:A7 and sales in C2:C7, this formula returns 400:

=SUMIFS(C2:C7,A2:A7,"North")+SUMIFS(C2:C7,A2:A7,"West")

This form is easy to read and works well when the list of allowed values is short. It also makes the logic visible to someone maintaining the workbook. If you add another region to the allowed set, add another SUMIFS result to the addition.

For a compact formula, Excel can calculate the subtotals for an array of criteria and then add them. With regions in A2:A7 and sales in C2:C7, this also returns 400:

=SUM(SUMIFS(C2:C7,A2:A7,{"North","West"}))

The outer SUM combines the results for North and West. The comma-separated items inside the braces form an array constant in this example. If your Excel regional settings use different list separators, the characters used in formulas and array constants may differ; use the separators expected by your installation. For clarity and compatibility across varied workbooks, the two-formula addition is often the simplest choice.

Repeat the range for “both conditions”

Repeated criteria ranges are appropriate when you want an AND test. For example, suppose A2:A7 contains scores 5, 12, 18, 20, 25, and 15, while B2:B7 contains corresponding sales of 100, 200, 150, 300, 250, and 120. To add sales for scores from 10 through 20, inclusive, the score must be at least 10 and at most 20. The matching sales are 200, 150, 300, and 120, totaling 770.

With scores in A2:A7 and sales in B2:B7, this formula returns 770:

=SUMIFS(B2:B7,A2:A7,">=10",A2:A7,"<=20")

The criteria range A2:A7 appears twice, once for each bound. Excel applies both tests to each score, so only rows passing both are included. The comparison operators are part of the criteria text, which is why they are enclosed in quotation marks. Use this pattern for other bounded ranges, such as quantities between two limits or dates from a start date through an end date.

Use cells to make criteria adjustable

Hard-coding limits is fine for a one-off calculation, but cell references make a worksheet easier to update. Put the lower score in E2 and the upper score in F2. With scores in A2:A7 and sales in B2:B7, this formula returns 770 when E2 is 10 and F2 is 20:

=SUMIFS(B2:B7,A2:A7,">="&E2,A2:A7,"<="&F2)

The ampersand joins each comparison operator to the value in its cell, producing criteria such as “greater than or equal to 10.” Change E2 or F2 to adjust the total without editing the formula. If a formula displays an unexpected result, confirm that the bound cells contain numbers rather than numbers stored as text.

The same approach works for dates. Put a start date in E2 and an end date in F2, and use the date column twice as the criteria range. Because Excel stores dates as serial numbers, cell references avoid having to build date criteria as text. For a period ending at midnight on the final day, an inclusive “less than or equal to end date” condition is suitable when the source cells contain dates without times. If the source contains timestamps, use a less-than condition for the day after the end date so records later on the final day are not missed.

Choose the right pattern for text and wildcards

For exact text choices, adding individual SUMIFS results is usually the clearest OR method. You can also use wildcard criteria when the goal is to match a text pattern rather than a particular list of labels. The asterisk matches any number of characters, while the question mark matches one character. For instance, a criterion of "App*" matches text beginning with “App,” such as “Apple” or “Applesauce.” A tilde can escape a wildcard when you need to search for a literal asterisk or question mark.

Wildcards do not turn multiple criteria pairs into OR logic. If you put two different exact-value criteria on the same range as separate pairs, Excel requires both to be true for a row. That cannot select a cell that contains one exact label or another mutually exclusive label. Use separate subtotals or an array of criteria for that case instead.

Be deliberate with broad patterns. A wildcard such as "*East*" may match labels that contain “East” anywhere, not just a region whose entire name is East. If your labels include spaces, suffixes, or inconsistent spelling, inspect the source data and choose a pattern that matches only the intended entries.

Build and check the formula step by step

  1. Identify the values to add, such as sales in C2:C100. This is the sum range.
  2. Identify the column containing the condition, such as regions in A2:A100. This is the criteria range.
  3. Decide whether the logic is AND or OR. Repeated criteria pairs in SUMIFS impose combined tests; separate subtotals represent alternatives.
  4. Keep the sum range and criteria ranges aligned. They should cover the same rows so each amount is evaluated against the correct record.
  5. Test the result with a small sample or calculate each component subtotal separately. This helps reveal a misspelled label, an incorrect range, or an unintended overlap.

For example, if a worksheet has a header in row 1, use data rows beginning at row 2 consistently. A sum range that starts in C2 and a criteria range that starts in A3 are misaligned: Excel will compare amounts and labels from different records. Whole-column references can be convenient, but using matching bounded ranges makes the data area easier to review and may improve calculation efficiency in large workbooks.

Avoid double counting and other common mistakes

Adding subtotals is correct when the alternatives do not overlap. But a record can be counted twice if one criterion also matches another. For instance, adding results for a broad wildcard such as “starts with N” and a separate North criterion counts North rows in both subtotals. Use non-overlapping criteria, or design a method that counts each qualifying row once.

  • Do not confuse AND with OR. Repeating a range with incompatible exact text conditions is not a way to request alternatives.
  • Check for extra spaces. “West” and “West ” may not behave like the same clean label in exact matching. Clean inconsistent source data where possible.
  • Check data types. A number stored as text or a date stored as text may fail to match an intended numeric or date condition.
  • Use consistent range sizes. Keep each criteria range aligned with the sum range and covering the same rows.
  • Look for hidden assumptions. A total of zero can mean no matches, but it can also mean that matching rows have blank or zero amounts.

SUMIFS is not case-sensitive for ordinary text matching, so criteria such as “north” and “North” are not a way to distinguish capitalization. If capitalization is meaningful in your data, a case-sensitive approach requires a different formula strategy. For most reporting tasks, standardizing labels is more practical than relying on case distinctions.

When SUMIFS is not the best fit

For a small fixed OR list, adding separate SUMIFS formulas is easy to audit. For a growing list of allowed categories, keeping the choices in worksheet cells can make the setup more maintainable, but the exact formula depends on how that list should be interpreted and on the Excel version in use. Another practical option is a PivotTable when you need summaries by category and want to explore or filter the results interactively rather than maintain a custom formula.

For AND conditions across several fields—such as region equals North and status equals Paid—use separate criteria-range/criterion pairs in one SUMIFS. For an OR across different fields, such as region equals North or status equals Paid, the logic is more complex; adding two subtotals may double-count records that satisfy both. In that case, choose a formula or data-preparation method that handles overlap explicitly rather than simply adding totals without checking.

In Microsoft 365 for Windows, enter the formula in a worksheet cell and press Enter. Excel for the web uses the same core SUMIFS syntax. On Mac, the function works similarly, although argument separators and decimal conventions can depend on the regional settings. If a formula copied from another computer is rejected, check whether your Excel expects commas or another list separator.

Frequently Asked Questions

Can SUMIFS use the same criteria range twice?
Yes. Repeat the range with a different criterion when both conditions must be true, such as a minimum and maximum. It does not make the conditions alternatives.

How do I sum rows where one column is either North or West?
Add one SUMIFS result for North to another for West, or use an array of the two criteria inside SUMIFS wrapped in SUM. Check that the alternatives do not overlap.

Does SUMIFS support OR criteria directly?
Its criteria pairs combine as AND. For OR choices in one column, calculate and add separate subtotals or use an array of criteria and aggregate the returned results.

Can I use two conditions on one column to sum between dates or numbers?
Yes. Repeat the same criteria range with a lower-bound test and an upper-bound test. Use cell references joined to operators if users need to change the bounds.

Why does my SUMIFS formula return zero?
Check whether the criteria use the intended AND or OR logic, whether labels contain extra spaces, whether values are stored as the expected data type, and whether all ranges cover the same rows. Also confirm that matching records contain numeric amounts to add.

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.