# Excel "IF" formula problem

Solved/Closed
vongypsy - Jul 2, 2009 at 02:44 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - 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.

## 6 replies

mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 161
Jul 2, 2009 at 11:15 PM
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 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))

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 Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 161
Jul 5, 2009 at 10:35 PM
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)
```

Didn't find the answer you are looking for?

BEGGGINER Posts 1 Registration date Tuesday December 8, 2009 Status Member Last seen December 8, 2009
Dec 8, 2009 at 07:47 PM
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 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 29, 2010 at 10:22 PM
Isnt the last If clause missing the value in case of "false" condition, I wonder if that is the reason