Search : in
By :

Excel "IF" formula problem

Last answer on Oct 30, 2009 5:21:54 pm GMT vongypsy, on Jul 2, 2009 7:44:03 pm BST 
 Report this message to moderators

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.

Configuration: Windows XP Internet Explorer 8.0

Best answers for « Excel "IF" formula problem » in :
EXCEL: Nested IF formula question (Solved) Show
Excel - IF formulas for Printing and Calculation Show Excel - IF formulas for Printing and Calculation Issue Solution Note Issue I am trying to get Excel IF formulas to print a text and calculation based on true/false. Such as, if I have some dates, and I don’t need to send a notice to a...
Having Excel IF formulas print text And calc Show
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
[Excel] – Countif Formula Show[Excel] – Countif Formula Issue Solution Note Issue I have one excel file in that Column "B" has the "pending or Closed" Status and Column "C" has "Severity 1, Severity 2 and Severity 3" I want to count in one cell with "Pending...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

mubashir aziz, on Jul 3, 2009 4:15:27 am BST

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


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

2

vongypsy, on Jul 4, 2009 2:00:49 am BST
  • +1

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)),"")

   
Reply to vongypsy

3

mubashir aziz, on Jul 6, 2009 3:35:33 am BST

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)





Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

4

joan, on Oct 30, 2009 12:56:47 am GMT
  • +1

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

   
Reply to joan

5

Trowa, on Oct 30, 2009 12:35:55 pm GMT

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

   
Reply to Trowa

6

 Joan, on Oct 30, 2009 5:21:54 pm GMT

Thank you Trowa. That helps!

   
Reply to Joan
Ask your question Reply
They need your help