Tue 15 Oct 2019

Tips & Tricks 173 - VBA - OR Condition in FIND

By |Tuesday, October 15th, 2019|Categories: Tips and Tricks, VBA|Tags: , , , |0 Comments

FIND is a very powerful function in VBA but it doesn't support OR condition. Hence, if you want to find say two values "A" or "B", then you can code an array within FIND. To do OR in FIND, you will need to use following code (this is a sample code only, there can be many variations of this code) (more…)

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.