## 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.

**Download Excel *.xlsx file**

Running totals within date range

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

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Sum values between two dates and based on a condition

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

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**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