Multiple IF statment help

[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

 

[Mike H]

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.

 

[Mark D]

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

 

[Joe User]

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)

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 -----