IF/SUM Formula

[mickey]

I am reading values from several worksheets that are compiling on a front
worksheet. However, when I try to sum those values my formula is not
recognizing the later cell values.
=IF(AB2>0,AB2,P2+S2+V2+Y2)
Anyone experience this and how did you resolve it?
By the way, I'm running Excel 07.
Thanks

 

[Mike H]

Hi,

There's nothing wrong with the formula so if AB2>0 evaluates as false then
it will sum the 4 cells providing they are numbers but if any are text you
will get a #VALUE! error. What error are you getting?

Mike

 

[Danny Boy]

You say:

"I am reading values from several worksheets that are compiling on a front
worksheet..."

If this is the case then I'd expect to see the sheet name referenced in your
formula, such as;

=C2+Sheet2!C2+Sheet3!C2

 

[mickey]

I'm getting a blank cell. The cells are all formatted as accounting. If I
change the formula from a 0 to "" I do get the #VALUE! error.

 

[Mike H]

Hi,

Then I think you have a couple of issues. Changing 0 to "" evaluates as
false and because you get a value error then some or all of your numbers in
P2 etc seem to be text. Try this for each of those cells

=isnumber(P2) and each should evaluate as true. If any evaluate as false
then they are text.

second consider AB2 is it really <0 or is there a small number that the
formatting of your formula cell or of AB2 stops you from seeing.

Mike

 

[mickey]

If I remove the statement and try to sum the 4 cells with a simple A+B+C+D
formula I get the #VALUE! error.
????

 

[mickey]

Mike:
I formatted each cell as a number and rewrote the formula to =P2+S2+V2+Y2
and I get a #VALUE! error.

 

[Mike H]

Formatting as a number won't change anything, if the cell contains text then
text it remains. How are these 'numbers' derived, what's the formula?

Mike

 

[mickey]

They are copied and pasted from another workbook.

 

[Mike H]

You could upload a copy of your workbook to the link below and post the link

http://www.savefile.com/

Mike

 

[mickey]

Here is the formula that reads the other worksheets:
='47'!$C$40

 

[NOPIK]

Here what I do to simple convert text with numeric value into a number
(on error set to skip casual text):
For Each c In ActiveSheet.UsedRange.Cells
On Error Resume Next
If c <> "" Then
c = CDbl(c)
End If
Next c
On Error GoTo 0

 

[Rick Rothstein]

Another method... Select all the cells containing "text numbers", Cell
Format them to General and then execute this command in VB's Immediate
Window...

Selection.Formula = Selection.Formula

If you want that as a macro...

Sub MakeTextNumbersToRealNumbers()
With Selection
.NumberFormat = "General"
.Formula = .Formula
End With
End Sub

Just select the cells to be converted and run the macro.