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
pass multiple conditions dynamically.xlsx
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, […]
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