Conditional divide (divide if)

[eendje]

B C D
10 1 5
8 0 6
4 1 4

This is a short version of my sheet. Column C only contains 1 or 0. Column B and D all sorts of numeric values.

For each cell in column A I want to check if the corresponding cell in column C contains a 1 and if so, I want it to divide the corresponding cell in column B with that one of column D. If the value in column C is 0, it should return 0

So in the example above, Column A would have the values 2 (10/5), 0 (since C=0) and 1 (4/4).

To be more accurate, I want to use this formula inside another formula, something like:
=1-$A2+$A2*(B4/D4+B6/D6+...+...for all where column C has value of 1)

Can anyone assist me with this please? I looked a bunch but couldn't find the solution.

 

[njimack]

=if(c1,b1/d1,0)

 

[Desu Nota from Columbus]

=if(c1=1, b1/d1,0)

 

[Desu Nota from Columbus]

oops didn't see njimack's response...I work for the department of redundancy department.

 

[eendje]

=if(c1=1, b1/d1,0)

Thanks, but not entirely what I meant.

I want devide all cells in column B with those in D when the corresponding cell in C=1, not just for that one cell.

So actually in the given example, the value of cell A1 would be: 3 (10/5+4/4)

 

[njimack]

=SUMPRODUCT(--(C1:C3=1),(B1:B3/D1:D3))
Change the ranges to fit your dataset, or consider using dynamic ranges

 

[eendje]

=SUMPRODUCT(--(C1:C3=1),(B1:B3/D1:D3))
Change the ranges to fit your dataset, or consider using dynamic ranges

That does exactly what I need, thanks!

I remember now seeing the sumproduct formula being used in similar ways before and always found this quite strange. As if it is 'misused' somehow.

Can u explain what this does, cause I'm trying to wrap my head around it. For instance, the '--' what does that do?

 

[pgc01]

... Column C only contains 1 or 0. ...

In that case, also:

=SUMPRODUCT(C1:C3,B1:B3/D1:D3)

Remark: these formulas assume that the values in D are not zero.

 

[eendje]

In that case, also:

=SUMPRODUCT(C1:C3,B1:B3/D1:D3)

Remark: these formulas assume that the values in D are not zero.

Also works just fine. I see what this does now. Still wonder about the '--' though

 

[pgc01]

... Still wonder about the '--' though ...

Check here:

http://www.mrexcel.com/forum/showthread.php?t=128907