Excel "IF" formula problem [Solved/Closed]

vongypsy - Jul 2, 2009 at 02:44 PM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Jan 29, 2010 at 10:22 PM
Hello,
I have a row of 4 cells each containing an "if formula" to get information from another worksheet. An example of the formula in these cells is: =IF('2009'!E2>0,'2009'!E2,""). My problem is trying to find the total of these cells. I'm adding 3 and subtracting 1. The formula I'm using is: =IF(SUM(C2,E2:F2)>0,SUM(C2-D2+E2+F2),""). The formula works great if all 4 cells have a value in it; however, if one is blank, #VALUE! shows in the cell. Is it possible to do what I'm trying to?

Thanks for any help.
See more 

9 replies

Best answer
mubashir aziz 191 Posts Sunday April 12, 2009Registration date February 16, 2010 Last seen - Jul 2, 2009 at 11:15 PM
6
Thank you
the problem is occurring because of "" "text" To avoid the "" error you can enter below formula

=IF(SUM(C2,E2:F2)>0,SUM(N(C2)-N(D2)+N(E2)+N(F2)),"")

OR

=IF(SUM(C2,E2:F2)>0,N(C2)-SUM(E2:F2),"")

both will work fine ....


Thank you, mubashir aziz 6

Something to say? Add comment

CCM has helped 1851 users this month

2
Thank you
Thank you so much, it worked!

May I ask, what does the N mean? I've never seen that before.

=IF(SUM(C2,E2:F2)>0,SUM(N(C2)-N(D2)+N(E2)+N(F2)),"")
1
Thank you
I tried doing the same thig by adding all the rows with FT and H but I still keep getting #Value result. What I am doing wrong?

=SUM(IF(($G$6:$G$1476="FT")*($F$6:$F$1476="H"),1,0))

Thanks in advance for your help!

Joan
Hi Joan,

You need to confirm your formula by pressing Control+Shift+Enter so that you get curly bracket around your formula. This will make your formula an array/CSE formula.

Best regards,
Trowa
Thank you Trowa. That helps!
mubashir aziz 191 Posts Sunday April 12, 2009Registration date February 16, 2010 Last seen - Jul 5, 2009 at 10:35 PM
0
Thank you
Here I used N() to convert the text into value "0". Below example will explain remain usage of N()

A2=7 
A3=Hello 
A4=TRUE 
A5=4/17/2008 

Formula Description (Result) 
=N(A2) Because A2 contains a number, it is returned (7) 
=N(A3) Because A3 contains text, 0 is returned (0, see above) 
=N(A4) Because A4 is the logical value TRUE, 1 is returned (1, see above) 
=N(A5) Because A5 is a date, the serial number is returned (varies with the date system used) 
=N("7") Because "7" is text, 0 is returned (0, see above) 
 





BEGGGINER 1 Posts Tuesday December 8, 2009Registration date December 8, 2009 Last seen - Dec 8, 2009 at 07:47 PM
0
Thank you
HI ,I need help with very simple formula that doesent work for me ,I'm not an expert so I tried some advices form this and some other forums but its still giving me error message,help:(

well my formula should look something like this

=IF(D11=1;F5,IF(D11=2;F6,IF(D11>2;0)))

maybe im useing old version but I have to input ; instead of , for the equasion to work,i also tried copy paste from some other posts but it still gives me error meassage ,ctrl+shift+enter dont work either...

is there any other way to do this??

Thanks a lot for help
Try this it works on excel 2007 but it should work on lower excels.

=IF(D11=1,F5,IF(D11=2,F6,IF(D11>2,0)))
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jan 29, 2010 at 10:22 PM
0
Thank you
Isnt the last If clause missing the value in case of "false" condition, I wonder if that is the reason