Excel "IF" formula problem [Solved/Closed]

Report
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
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.

6 replies

Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
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 ....


6
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3851 users have said thank you to us this month

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)),"")
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!
Posts
191
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
210
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) 
 





Posts
1
Registration date
Tuesday December 8, 2009
Status
Member
Last seen
December 8, 2009

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)))
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Isnt the last If clause missing the value in case of "false" condition, I wonder if that is the reason