How to Use SUMIF with OR in Google Sheets
Using SUMIF with OR in Google Sheets
SUMIF function in Google Sheets allows you to sum the values in a range based on one criterion, but if you want to use multiple criteria with OR logic, you'll need to use a combination of SUMIF functions or use the FILTER and SUM functions.
Here's a step-by-step guide on how to use SUMIF with OR in Google Sheets:
Method 1: Using multiple SUMIF functions
- Open your Google Sheet.
- Identify the range where you want to apply the SUMIF function.
- Choose a cell where you want the result to appear.
- Use the following formula structure to combine multiple SUMIF functions with OR logic:
=SUMIF(range, criterion1, [sum_range]) + SUMIF(range, criterion2, [sum_range]) - SUMIF(range, criterion1 AND criterion2, [sum_range])Replace range with the range you want to apply the criteria to, criterion1 and criterion2 with your specific criteria, and [sum_range] with the range of numbers you want to sum. If the [sum_range] is the same as the range, you can omit it from the formula.
- Press Enter to apply the formula.
Example
Let's say we have a sales data table and we want to find the total sales for products A and B.
| Product | Sales |
|---|---|
| A | 100 |
| B | 200 |
| C | 300 |
| A | 400 |
| B | 500 |
- In an empty cell, use the following formula:
=SUMIF(A2:A6, "A", B2:B6) + SUMIF(A2:A6, "B", B2:B6)- Press Enter to apply the formula.
The result will be 1200, which is the sum of sales for products A and B.
Method 2: Using FILTER and SUM functions
- Open your Google Sheet.
- Identify the range where you want to apply the SUMIF function.
- Choose a cell where you want the result to appear.
- Use the following formula structure to combine FILTER and SUM functions with OR logic:
=SUM(FILTER(sum_range, (range=criterion1) + (range=criterion2)))Replace range with the range you want to apply the criteria to, criterion1 and criterion2 with your specific criteria, and sum_range with the range of numbers you want to sum.
- Press Enter to apply the formula.
Example
Using the same sales data table as before, we want to find the total sales for products A and B.
- In an empty cell, use the following formula:
=SUM(FILTER(B2:B6, (A2:A6="A") + (A2:A6="B")))- Press Enter to apply the formula.
The result will be 1200, which is the sum of sales for products A and B.