## Sum based on OR – AND logic

**Question:**

It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum))

But all these are single conditions -- you can't pass multiple conditions, say for example: USA or China or France in column_countries and Airbus or Boeing in column_producer. I know 2 solutions to get around this limitation, but none is perfect:

1) =SUM((column_plane=TRUE)*((column_countries="USA")+(column_countries="China")+(column_countries="France"))* ((column_producer="Airbus") + (column_producer="Boeing"))*(column_to_sum)) -- this one works great, but it's not dinamic at all. What if the user chooses 10 multiple countries or more? You're not gonna write tens of equations for each condition.

2) =SUM((column_countries= IF(TRANSPOSE(contries_selected)=TRUE, TRANSPOSE(countries_selected_names)))*(column_producer="Airbus")*(column_to_sum)) -- that works fine, but this is producing a 2-dimentional matrix and hence is good for one condition only -- notice column_producer has only one value. Now, what if you want to pass multiple values to column_producer as well?

In SQL this equates to

SELECT SUM(column_to_sum)

FROM table

WHERE

(country = "USA" OR country = "France" OR country = "China")

AND

(producer ="Boeing" OR producer="Airbus")

Any idea how to replicate that in Excel???

You can find the question here.

**Answer:**

The criteria is in B20:D25, I have colored the cells that match. Here is how to avoid writing ten's of equations:

Formula in C26:

### Explaining formula in cell C26

#### Step 1 - Identify criteria B20:B25 in B3:B15

The COUNTIF function counts values based on a condition or criteria. If a match is found 1 is returned and if not found then it returns 0 (zero).

COUNTIF(B20:B25, B3:B15)

becomes

COUNTIF({"A380"; "PA-18 Super Cub"; "Continental C-90-8F"; "Lycoming O-320"; 390; 0},{"A380"; 747; 150; "PA-18 Super Cub"; "172 Skyhawk"; "T-37 Tweet"; "Continental C-90-8F"; 1900; 747; 390; 400; "Lycoming O-320"; 2000})

and returns

{1; 0; 0; 1; 0; 0; 1; 0; 0; 1; 0; 1; 0}

#### Step 2 - Identify criteria C20:C25 in C3:C15

COUNTIF(C20:C25, C3:C15)

becomes

COUNTIF({"US"; "Great Britain"; "Russia"; 0; 0; 0},{"US"; "China"; "France"; "Great Britain"; "Australia"; "Russia"; "US"; "China"; "France"; "Great Britain"; "Australia"; "Russia"; "Germany"})

and returns

{1; 0; 0; 1; 0; 1; 1; 0; 0; 1; 0; 1; 0}

#### Step 3 - Identify criteria D20:D25 in D3:D15

COUNTIF(D20:D25,D3:D15)

becomes

COUNTIF({"Airbus"; "Piper"; 0; 0; 0; 0},{"Airbus"; "Boeing"; "Cessna"; "Piper"; "Cessna"; "Cessna"; "Piper"; "Beechcraft"; "Boeing"; "Beechcraft"; "Beechcraft"; "Piper"; "Beechcraft"})

and returns

{1;0;0;1;0;0;1;0;0;0;0;1;0}

#### Step 4 - Multiply arrays

All conditions must be TRUE in order to return TRUE.

COUNTIF(B20:B25,B3:B15)*COUNTIF(C20:C25,C3:C15)*COUNTIF(D20:D25,D3:D15)

becomes

{1; 0; 0; 1; 0; 0; 1; 0; 0; 1; 0; 1; 0}*{1; 0; 0; 1; 0; 1; 1; 0; 0; 1; 0; 1; 0}*{1;0;0;1;0;0;1;0;0;0;0;1;0}

and returns

{1;0;0;1;0;0;1;0;0;0;0;1;0}

#### Step 5 - Multiply array with amounts

COUNTIF(B20:B25, B3:B15)*COUNTIF(C20:C25, C3:C15)*COUNTIF(D20:D25, D3:D15)*E3:E15

becomes

{1;0;0;1;0;0;1;0;0;0;0;1;0}*E3:E15

becomes

{1; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0}*{800; 400; 800; 800; 500; 300; 800; 700; 500; 400; 500; 200; 900}

and returns

{800;0;0;800;0;0;800;0;0;0;0;200;0}

#### Step 6 - Sum array

The SUMPRODUCT function then adds the numbers and returns a total.

SUMPRODUCT(COUNTIF(B20:B25, B3:B15)*COUNTIF(C20:C25, C3:C15)*COUNTIF(D20:D25, D3:D15)*E3:E15)

becomes

SUMPRODUCT({800;0;0;800;0;0;800;0;0;0;0;200;0})

and returns 2600. 800 + 800 + 800 + 200 = 2600.

**Get Excel *.xlsx file**

### Sum category

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]

This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13.

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

I will in this article demonstrate different ways to sum values, the first method is so easy and fast it's […]

This article explains why your formula is not working properly, there are usually four different things that can go wrong. […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 2 Responses to “Sum based on OR – AND logic”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This can be solved without using array in the same fashion (makes the file much lighter when compared to array) =sumproduct((logical test on array1)*(logical test on array2)*...*(result array))

Vipul,

Thanks!

Formula in C26:

=SUMPRODUCT(COUNTIF(B20:B25, Model)*COUNTIF(C20:C25, Country)*COUNTIF(D20:D25, Plane)*Amount) + Enter