Which function will shorten IF-THEN-ELSE ?

[Jack Ryan]

Here's a simplified example of what I want to do:

Col A Col B
Row 1 yes 5
Row 2 10
Row 3 yes 15
Row 4 yes 20

What is the formula that will automatically find all numbers in Col B
that have a corresponding "yes" in Col A, then SUM them and multiply
by 30% ? If done manually, the final result would then be the
equivalent of =(5+15+20)*.30, which is 12.

Thanks,

 

[Jack R]

Here's a simplified example of what I want to do:

             Col A      Col B
Row 1     yes           5
Row 2                    10
Row 3     yes         15
Row 4     yes         20

What is the formula that will automatically find all numbers in Col B
that have a corresponding "yes" in Col A, then SUM them and multiply
by 30% ?  If done manually, the final result would then be the
equivalent of =(5+15+20)*.30, which is 12.

Thanks,

Is this formula the best correct answer?
=SUMIF(A1:A4,"yes",B1:B4)*0.3
Could a LOOKUP or OFFSET function be used to achieve the same answer?

Thanks,

 

[Mike H]

Hi,

Use this

=SUMPRODUCT((A1:A10="Yes")*(B1:B10))*0.3
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Roger Govier]

Hi Jack

An alternative would be
=SUMPRODUCT((A1:A4="yes")*B1:B4)*0.3

Your formula is fine, and SUMIF is far more efficient than Sumproduct,
so on a large amount of data I would expect it to be considerably faster.

 

[Jack R]

Hi Jack

An alternative would be
=SUMPRODUCT((A1:A4="yes")*B1:B4)*0.3

Your formula is fine, and SUMIF is far more efficient than Sumproduct,
so on a large amount of data I would expect it to be considerably faster.

I was just wondering how LOOKUP and OFFSET could be helpful in a
larger version of my example. What would my example look like for
LOOKUP and OFFSET to be useful?

 

[Roger Govier]

Hi Jack

Offset is a volatile function, and on a large spreadsheet is to be
avoided wherever possible. It creates a huge overload in processing and
will slow the sheet down enormously.

Sumif is one of the most efficient functions, and your proposed solution
is the best - IMHO