Multiple IF statment help

  • Thread starter Thread starter Mark D
  • Start date Start date
M

Mark D

Evening everyone

I am sure this is simple but it's late and I'm very tired.

I have the following IF statment but I need all three to be added together -
the sum of D147,F147 & H147

=IF($C147<J$3,$D147,"")+IF($E147<J$3,$F147,"")+IF($G147<J$3,$H147,"")

All I am getting is a VALUE come up.

Thanks
 
Hi,

You were very close, try this

=IF($C147<J$3,$D147,0)+IF($E147<J$3,$F147,0)+IF($G147<J$3,$H147,0)
--
Mike

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

Yep that works, although I just found why it's taken so long for me to work
out

In the formula you have helped me with

=IF($C147<J$3,$D147,0)+IF($E147<J$3,$F147,0)+IF($G147<J$3,$H147,0)

D147, F147 & H147 sometimes have no value in them and are blank, the
following forumula exists in those cells

=IF(ISBLANK($H104),"",$H104)

If I remove this formula then of course your formula works. But I need te
formula in cells d147 ETC to stay

Thanks again for your help
 
Mark D said:
In the formula you have helped me with
=IF($C147<J$3,$D147,0)+
IF($E147<J$3,$F147,0)+
IF($G147<J$3,$H147,0)

D147, F147 & H147 sometimes have no value
in them and are blank, the following forumula
exists in those cells
=IF(ISBLANK($H104),"",$H104)
Click to expand...

So they do have a value, namely the null string. They only appear to be
blank.

Try:

=($C147<J$3)*N($D147)+
($E147<J$3)*N($F147)+($G147<J$3)*N($H147)

But what about C147, E147, G147 and J3. Are those numeric? If so, could
those also contain the null string value ("")?

If yes, note that even though you do not get an Excel error like #VALUE, the
formulas might still be wrong because in a comparison between text ("") and
number, text is always considered larger(!).

So you might want:

=(N($C147)<N(J$3))*N($D147)+
(N($E147)<N(J$3))*N($F147)+
(N($G147)<N(J$3))*N($H147)


----- original message -----
 
Back
Top