Sun 25 Oct 2015

Solution - Challenge 26 – Find Sum given multiple OR conditions across columns

By |Sunday, October 25th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns.

Put following formula for the result -

=SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+
COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20))

The workbook illustrating the solution can be downloaded from Solution - Challenge 26 – Find Sum given multiple OR conditions across columns.

Sat 26 Sep 2015

Challenge 26 - Find Sum given multiple OR conditions across columns

By |Saturday, September 26th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

-- The Excel file related to this challenge can be downloaded from Challenge - Multiple OR Conditions ---

Given your data below, you need to find the total sales for cities given in F1 to I1 and zones given in F2 to I2. Either city has to match in F1 to I1 or zone should match in F2 to I2. If both city and zone match, it should be considered once only not twice.

Seattle East 20
Seatltle West 10
Miami East 30
Denver South 50

The answer for above for Seattle and East should be 20+10+30 = 60 not 20+20+10+30 = 80

Multiple OR Conditions

You may post your answer in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 25-Oct-15.