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