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