How to Sum values using an OR condition across multiple columns, including using OR with AND conditions. For instance, sum all the values that have **red** in column A or **West** in column B; another example is to sum the values with **red** in column A and **West** in column B or those with just **West** in column B.

This all sounds a bit confusing so let's get to some examples.

This tutorial assumes that you are familiar with the SUM, IF, and SUMIFS functions.

Sum with AND Criteria (Default Method)

Get a Better Understanding of How these Formulas Work

Add values that meet a condition in one column or another condition in another column.

Let's Sum the values the have **red** in column A or **West** in column B. This also includes values that have both red and West, but it does not double-count them.

```
=SUM(IF((A1:A6="red")+(B1:B6="west"),1,0)*C1:C6)
```

Result:

**Array Formula** - this is an array formula and so you must enter it into the cell using **Ctrl **+ **Shift** + **Enter**.

The formula looks scary and confusing but there are only two parts that you have to know how to change, the criteria section and how many values across how many criteria's to check.

**(A1:A6="red")**

This code is the syntax that you use to check for a value in a range. The range that contains the criteria goes on the left and the value that you check for goes on the right.

Enclose criteria text with double quotation marks, but if you use numbers for the criteria, you don't have to enclose it with quotation marks.

In the formula you will notice that the criteria sections, as described just above here, are combined using a plus sign:

**(A1:A6="red")+(B1:B6="west")**

To add more columns with more criteria, simply add more sections following this pattern, using a plus sign between them.

This situation combines OR criteria with AND criteria.

Add values that have **red** and **West** values for column A and B or values that have only **West** in column B.

```
=SUM(IF((A1:A6="red")*(B1:B6="west")+(B1:B6="west"),1,0)*C1:C6)
```

Result:

**Array Formula** - this is an array formula and so you must enter it into the cell using **Ctrl **+ **Shift** + **Enter**.

**(A1:A6="red")**

(This is the same as the last example.)

This code is the syntax that you use to check for a value in a range. The range that contains the criteria goes on the left and the value that you check for goes on the right.

Enclose criteria text with double quotation marks, but if you use numbers for the criteria, you don't have to enclose it with quotation marks.

In the formula you will notice that the criteria sections, as described just above here, are combined using either a plus sign or a multiplication sign:

**(A1:A6="red")*(B1:B6="west")+(B1:B6="west")**

The multiplication sign acts like **AND** and means that a value must have BOTH criteria; it must have **red** in column A and **West** in column B.

The plus sign acts like **OR** and means that the value will also be included if only **West** is in column B.

Put those two together and it reads like this: sum the values where **red** and **West** are in column A OR only **West** is in column B.

If you want to add more criteria sections where values must be in both columns, use the multiplication sign to join them; if you want to add more criteria sections where a value can be present in just a single column, use the plus sign to join them.

You need to download the sample file attached to this tutorial so you can work with these examples in order to get a better understanding of them.

I included this here just so you wouldn't think that I left it out, but this method just uses the default **SUMIFS** function that is available in Excel 2007 and later versions.

In this example, let's sum the values that have **red** in column A and **West** in column B.

```
=SUMIFS(C1:C6,A1:A6,"red",B1:B6,"west")
```

Result:

**C1:C6** is the range of values that will be added together.

**A1:A6** is the first criteria range.

**"red"** is the value that should appear in the first criteria range.

**B1:B6** is the second criteria range.

**"west"** is the value that should appear in the second criteria range.

You follow this pattern to add as many criteria as you need, up to 127.

This is a regular formula and there isn't anything special about it or how you enter it, like there is for an array formula.

The tricky stuff in this tutorial comes from using array formulas in Excel. These formulas are, almost by definition, complex and confusing and annoying, but so darn powerful.

One way to try and learn a little more about how they work is to see how Excel calculates these formulas; now this won't tell you everything, but it might help a little.

Select one of the array formulas and then go to the **Formulas** tab and click **Evaluate Formula**.

You will then be able to go through the formula to see how it works; just click the **Evaluate** button.

You can learn more about this feature here in our tutorial for the Evaluate Formula feature.

You cannot enter an array formula by typing curly braces around it! You will see these braces when you select an array formula and look to the Formula Bar but you cannot add or remove them yourself.

The only way to enter an array formula is by using Ctrl + Shift + Enter.

Array formulas and these examples can be really tricky and take some time to get used to, but, once you learn them, they can save you a lot of time.

Make sure you download the sample file attached to this tutorial so you can work with these examples and get a better understanding of how this works in Excel.

Similar Content on TeachExcel

Sum Values that Meet 1 of Multiple Conditions in Excel

Tutorial: How to sum values that equal one of many potential criteria; this is basically summing wi...

Tutorial: How to sum values that equal one of many potential criteria; this is basically summing wi...

How to use Vlookup Across Multiple Worksheets in Excel

Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...

Tutorial: This tutorial shows you how to use the Vlookup function across multiple worksheets within ...

Center Titles Across Multiple Cells in Excel

Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...

Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...

SUMIFS - Sum Values Based on Multiple Criteria in Excel

Tutorial: The SUMIFS function allows you to sum values that meet multiple criteria across multiple ...

Tutorial: The SUMIFS function allows you to sum values that meet multiple criteria across multiple ...

Complex Structured References (Table Formulas) in Excel

Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...

Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...

Vlookup Across Multiple Workbooks

Tutorial: How to use the VLOOKUP function across multiple workbooks in Excel. This will create a lin...

Tutorial: How to use the VLOOKUP function across multiple workbooks in Excel. This will create a lin...