Example: I need a cell J3 to calculate as follows:

If H3 is >0 and/or <8, J3 should be the sum of 8-H3

IF H3 is =0 J3 should be 0

IF H3 is >8, J3 should be 0I have come up with =IF(H3<8,SUM(8-H3),IF(H3>8,SUM(0)))

I can't figure out how to modify the H3<8 part to be if it is >0 but <8 so that I can then add IF(H3=0,0)

Assuming that H3<0 is never going to happen, try this: =IF(OR(H3=0,H3>8),0,8-H3)

You've asked for 2 specific conditions where the result should be 0: H3=0 or H3>8, so the OR function handles those. If

eitherof those arguments are TRUE, the OR is TRUE and 0 will be returned.If both H3=0 and H3>8 are FALSE, then H3 must be >0 and <=8, so there is no reason to specifically test for H3<=8.

P.S. The SUM functions in your formula serve no purpose other than to slow Excel down. Excel knows what to do with 8-H3 and 0.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

You are AWESOME! Thank you SOOOOO much. That worked perfectly. :)

Ask Your Question

Weekly Poll