SUMIF with OR in Google Sheets: Formulas That Work

To use SUMIF with OR logic in Google Sheets, add the results of separate SUMIF formulas, or use SUM around a SUMIF with an array of criteria. For example, if A2:A100 contains statuses and B2:B100 contains amounts, =SUM(SUMIF(A2:A100,{"Paid","Pending"},B2:B100)) totals rows marked either Paid or Pending.

What OR logic means in a SUMIF formula

SUMIF evaluates one condition: it checks a criteria range, identifies the cells that meet the criterion, and adds the corresponding cells from a sum range. OR logic means a row qualifies when it matches at least one of several alternatives. If the alternatives are statuses, a total might include both Paid and Pending entries while excluding every other status.

A common source of confusion is the difference between OR and AND. OR accepts a match to one condition or another. AND requires all specified conditions to be true at the same time. SUMIF handles one condition; SUMIFS handles multiple conditions together using AND logic. To get OR behavior, either calculate multiple qualifying totals and combine them, or give SUMIF an array of criteria and add the returned results.

In the opening example, the formula adds the amounts for Paid rows and Pending rows. The criteria range and sum range should cover corresponding rows and should have the same dimensions. If one range starts on row 2 and the other on row 1, the result may not represent the intended records.

Two reliable ways to sum alternative criteria

The easiest approach to read and maintain is adding individual SUMIF results. With statuses in A2:A100 and amounts in B2:B100, the formula is =SUMIF(A2:A100,"Paid",B2:B100)+SUMIF(A2:A100,"Pending",B2:B100). It calculates the Paid total and the Pending total separately, then adds them. This is useful when there are only a few alternatives or when a future editor needs to see each condition plainly.

A compact alternative uses an array of criteria. With the same status and amount layout, =SUM(SUMIF(A2:A100,{"Paid","Pending"},B2:B100)) asks SUMIF to evaluate both criteria and SUM to combine the results. This is convenient when the alternatives are short and fixed. Google Sheets supports array results from functions such as SUMIF, which SUM can aggregate.

Both formulas return the same answer when each row can match only one of the listed values. If the status in A4 is Paid, its B4 amount is included once; if A5 is Pending, its amount is also included. A row cannot simultaneously have two different exact text values in a single status cell, so these status alternatives do not double-count rows.

Use separate SUMIF formulas when clarity and easy editing matter most.
Use an array of criteria when you want a shorter formula for a small, fixed set of alternatives.
Use a different approach when the OR conditions refer to different columns or can overlap.

Using OR conditions across different columns

OR is more complicated when a row qualifies through different fields—for example, include a sale if its region is North or its channel is Online. Separate SUMIFS calculations can be added, but a row meeting both conditions would be counted twice. That is a mathematical overlap issue, not a Google Sheets error.

Suppose C2:C100 contains regions, D2:D100 contains channels, and B2:B100 contains sale amounts. To total each row once if the region is North or the channel is Online, use =SUM(FILTER(B2:B100,((C2:C100="North")+(D2:D100="Online"))>0)). The additions turn TRUE/FALSE tests into numbers, and the greater-than-zero test keeps a row if either test succeeds—or if both do. FILTER returns each qualifying amount once, so rows satisfying both conditions are not duplicated.

FILTER returns an error if it finds no matching rows. With the same layout, use =IFNA(SUM(FILTER(B2:B100,((C2:C100="North")+(D2:D100="Online"))>0)),0) if the desired result for no matches is zero. This formula uses IFNA to replace the no-match error; it does not conceal unrelated errors such as a misspelled function or an invalid range.

If the intent is to count a row twice when both conditions match, adding two SUMIFS results may be appropriate. If the intent is a total of unique qualifying rows, use a row-level OR test as above. Decide which interpretation you need before selecting a formula.

Adding numeric, date, and wildcard criteria

SUMIF criteria can be numbers, text, comparison expressions, or wildcard patterns. For example, criteria such as “>=100” can select values at least 100, and a text pattern with an asterisk can match a group of labels. When a comparison operator is combined with a cell reference, join the operator and cell value with an ampersand.

With dates in A2:A100, amounts in B2:B100, and the start date in E1, this formula totals dates on or after E1: =SUMIF(A2:A100,">="&E1,B2:B100). To include dates in either of two periods, add two date-based SUMIF calculations. With period start and end dates in E1:F1 and G1:H1, respectively, and dates and amounts in the same ranges, use =SUMIFS(B2:B100,A2:A100,">="&E1,A2:A100,"<="&F1)+SUMIFS(B2:B100,A2:A100,">="&G1,A2:A100,"<="&H1). Each SUMIFS applies both date boundaries to its own period. If the periods overlap, dates in the overlap are counted twice, so use non-overlapping period boundaries or adjust the logic.

For text, an asterisk (*) represents any sequence of characters and a question mark (?) represents one character in a wildcard criterion. To total amounts for labels beginning with North or West, with labels in A2:A100 and amounts in B2:B100, use =SUM(SUMIF(A2:A100,{"North*","West*"},B2:B100)). Wildcards are useful for controlled naming patterns, but can include more labels than intended: North* also matches North-East and Northern, for example. For exact matches, use the full text without a wildcard.

Choosing a formula for your sheet

Choose based on where the conditions live, how many there are, and whether overlap is possible. A few exact alternatives in one column are a natural fit for SUMIF with separate criteria. Conditions distributed across columns call for a row-level test if each record should count once. A long or frequently changing list of acceptable labels is often easier to maintain in cells than embedded inside a formula.

Two or three fixed text values in one column: add separate SUMIF results or use an array of criteria.
Conditions in separate columns: use FILTER with a combined OR test to keep matching rows once.
A variable list of allowed values: store the values in a range and consider SUMIF with that criteria range, wrapped in SUM.
Several conditions that all must hold: use SUMIFS, since that is AND logic.
Overlapping numeric or date groups: check whether the intended total counts qualifying rows once or counts each matching group.

For a criteria list in E2:E5, status values in A2:A100, and amounts in B2:B100, you can use =SUM(SUMIF(A2:A100,E2:E5,B2:B100)). This makes the allowed statuses editable without changing the formula. Keep the criteria list free of unintended blanks and duplicates. A blank criterion can match blank cells, and duplicate criteria can cause a matching status to be added more than once.

SUMIF versus SUMIFS and other options

SUMIF is designed around one criterion, while SUMIFS supports multiple criteria ranges and criteria. In SUMIFS, conditions in different arguments are combined as AND: a row must satisfy all of them. That makes SUMIFS a good choice for questions such as “sum amounts where status is Paid and region is North,” but it does not by itself mean “Paid or North.” To express OR with SUMIFS, calculate separate branches and account for any rows that satisfy both branches.

FILTER is useful when the test is about each record as a whole, particularly for OR conditions across columns. It returns matching rows or values, which can then be summed. SUMPRODUCT can also evaluate array-based conditions, but its formulas can be harder to read and maintain. For most ordinary totals, start with SUMIF, SUMIFS, or FILTER rather than choosing a more complex construction just because it fits on one line.

Google Sheets is a browser-based spreadsheet, and these examples use Google Sheets function names and formula behavior. Excel also has SUMIF and SUMIFS, but array handling and some formula details can vary by version and context; do not assume that a Sheets formula will behave identically in every Excel version. In either program, test the result against a small set of known rows when the workbook is important.

Common mistakes and troubleshooting

If the result is zero or unexpectedly small, first check that the criteria exactly match the underlying values. A status may contain a trailing space, or a number that looks like a number may actually be stored as text. Visible formatting does not always reveal the stored value. Compare a few source cells directly and check for extra spaces or inconsistent labels.

Ranges have different sizes: keep criteria and sum ranges aligned, such as A2:A100 and B2:B100.
Criteria are accidentally joined as AND: separate SUMIF calculations with plus signs express alternative branches; multiple SUMIFS conditions within one formula require all conditions.
Rows are double-counted: adding branches can count a row more than once when it satisfies multiple conditions. Use FILTER with a combined row-level OR test when each row should count once.
Numbers or dates are text: text-formatted values may not compare as expected. Check the cell contents and use consistent data types.
Array separators differ: formulas here use comma argument separators and a comma-separated horizontal array constant. Google Sheets locale settings can require different separators; follow the syntax the sheet accepts.

Also verify that the sum range contains the values you intend to add. If the sum_range argument is omitted, SUMIF sums the criteria range itself, which is often not the desired result when labels and amounts are in separate columns. For a quick audit, filter the source table to one condition and compare its visible amount total with that branch of the formula.

Frequently Asked Questions

Can SUMIF use OR criteria in Google Sheets?
Yes. Add separate SUMIF results, or use SUM around SUMIF with an array of criteria. For alternatives in different columns, use a row-level OR test, such as FILTER with combined TRUE/FALSE conditions, to avoid counting a row twice.

Does SUMIFS use OR or AND?
SUMIFS applies AND logic across its criteria: a row must satisfy each specified condition. To get OR behavior, add separate SUMIFS branches, while checking whether overlapping conditions would double-count rows.

How do I sum a list of possible text values?
Put the permitted values in a criteria range and pass that range to SUMIF, with SUM around the result. For example, with the values listed in E2:E5, statuses in A2:A100, and amounts in B2:B100, use =SUM(SUMIF(A2:A100,E2:E5,B2:B100)). Remove duplicates from the list to prevent repeated totals.

Why does my OR formula return twice the expected total?
A row may satisfy more than one branch, and adding branch totals counts it once in each branch. This often happens when the conditions are in different columns. Use a combined row-level OR test if each matching record should contribute only once.

Can I use OR with dates or numbers?
Yes. Calculate a SUMIF or SUMIFS result for each date range or numeric condition and add the results. Make the ranges non-overlapping if each record should be included only once, or account explicitly for any overlap.

A practical way to check the result

Before relying on a complicated OR total, validate the logic with a small sample. Write down which rows should qualify, check that each one is counted the intended number of times, and compare their amounts with the formula result. For a status list, temporarily filter the table to the allowed statuses and compare the visible records. For conditions across columns, inspect rows that meet both conditions as well as rows that meet just one; those overlap cases reveal whether the formula matches your intended definition.

Once the calculation is correct, make it easier to maintain: use consistent labels, align ranges, keep editable criteria in a clearly labeled list, and document whether duplicate matches are supposed to count once or more than once. Those small choices make OR formulas easier to audit when the sheet grows or another person inherits it.

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.