## Sum based on multiple criteria

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

Here is how to avoid writing ten's of equations:

**Array formula in C26:**

I have colored the cells that match.

**Named ranges**

Model (B3:B15)

Country (C3:C15)

Plane (D3:D15)

Amount (E3:E15)

What is named ranges?

**Download excel example fil****e**

pass multiple conditions.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

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 […]

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

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 […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

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 […]

I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]

Sum values containing text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

### 2 Responses to “Sum based on multiple criteria”

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