I have two sheets in a work book

SHEET ONE providing source data ex: A1 to A3 is changing and so is SUM A1:A3
(example are three sets of A1:A3 data)
And another factor lets say B1 is also changing.

A A A---------B
1 1 2 3 4
2 1 2 3
3 1 2 3
4 3 6 9

SHEET TWO ( IF F1=B1 then A1='sheet1' A4 .... and so on for B1='sheet' A4,
C1='sheet1'A4 ... REMEMBER that B1 and F1 is constantly changing so that the
data (A4) being plugged appropriately )

A B C................F
1 3 6 9................4

So if I do Isnumber, only the last (C1) shown as TRUE, all other isnumber=0
(even though it still shows the number)

Now I want to sum A1:C1 (sheet 2), it only give me Zero "0" , IS THERE ANY
WAY that I GET AROUND and SUM them ???

You are using IF formula... what is the ELSE path in IF F1=B1 then
A1='sheet1' A4?
I mean what do you have when F1 is not equal to B1?

SUM only sums up numeric values.. What you are getting as a result of your
formula is a text value hence the 0 sum (your isnumber() points to that...

Share the complete IF statement and values in the cell it refers to (one set
only)

"americasrecycler" wrote:

I have two sheets in a work book

SHEET ONE providing source data ex: A1 to A3 is changing and so is SUM A1:A3
(example are three sets of A1:A3 data)
And another factor lets say B1 is also changing.

A A A---------B
1 1 2 3 4
2 1 2 3
3 1 2 3
4 3 6 9

SHEET TWO ( IF F1=B1 then A1='sheet1' A4 .... and so on for B1='sheet' A4,
C1='sheet1'A4 ... REMEMBER that B1 and F1 is constantly changing so that the
data (A4) being plugged appropriately )

A B C................F
1 3 6 9................4

So if I do Isnumber, only the last (C1) shown as TRUE, all other isnumber=0
(even though it still shows the number)

Now I want to sum A1:C1 (sheet 2), it only give me Zero "0" , IS THERE ANY
WAY that I GET AROUND and SUM them ???

Hi Sheeloo,

ELSE Path for F1=B1 is A1=A1 (that's why the number shows but isnumber=0)

thanks



"Sheeloo" wrote:

You are using IF formula... what is the ELSE path in IF F1=B1 then
A1='sheet1' A4?
I mean what do you have when F1 is not equal to B1?

SUM only sums up numeric values.. What you are getting as a result of your
formula is a text value hence the 0 sum (your isnumber() points to that...

Share the complete IF statement and values in the cell it refers to (one set
only)

"americasrecycler" wrote:

I have two sheets in a work book

SHEET ONE providing source data ex: A1 to A3 is changing and so is SUM A1:A3
(example are three sets of A1:A3 data)
And another factor lets say B1 is also changing.

A A A---------B
1 1 2 3 4
2 1 2 3
3 1 2 3
4 3 6 9

SHEET TWO ( IF F1=B1 then A1='sheet1' A4 .... and so on for B1='sheet' A4,
C1='sheet1'A4 ... REMEMBER that B1 and F1 is constantly changing so that the
data (A4) being plugged appropriately )

A B C................F
1 3 6 9................4

So if I do Isnumber, only the last (C1) shown as TRUE, all other isnumber=0
(even though it still shows the number)

Now I want to sum A1:C1 (sheet 2), it only give me Zero "0" , IS THERE ANY
WAY that I GET AROUND and SUM them ???

Try testing the numbers on Sheet1 with ISNUMBER....

I suspect they are stored as text...

You can manually enter the numbers and test your formula

"americasrecycler" wrote:

Hi Sheeloo,

ELSE Path for F1=B1 is A1=A1 (that's why the number shows but isnumber=0)

thanks



"Sheeloo" wrote:

You are using IF formula... what is the ELSE path in IF F1=B1 then
A1='sheet1' A4?
I mean what do you have when F1 is not equal to B1?

SUM only sums up numeric values.. What you are getting as a result of your
formula is a text value hence the 0 sum (your isnumber() points to that...

Share the complete IF statement and values in the cell it refers to (one set
only)

"americasrecycler" wrote:

I have two sheets in a work book

SHEET ONE providing source data ex: A1 to A3 is changing and so is SUM A1:A3
(example are three sets of A1:A3 data)
And another factor lets say B1 is also changing.

A A A---------B
1 1 2 3 4
2 1 2 3
3 1 2 3
4 3 6 9

SHEET TWO ( IF F1=B1 then A1='sheet1' A4 .... and so on for B1='sheet' A4,
C1='sheet1'A4 ... REMEMBER that B1 and F1 is constantly changing so that the
data (A4) being plugged appropriately )

A B C................F
1 3 6 9................4

So if I do Isnumber, only the last (C1) shown as TRUE, all other isnumber=0
(even though it still shows the number)

Now I want to sum A1:C1 (sheet 2), it only give me Zero "0" , IS THERE ANY
WAY that I GET AROUND and SUM them ???

It's a pity that you seem to have deserted your earlier thread despite the
patience of 2 responders who hung in there with you to try and get you going:

http://tinyurl.com/cry7ba

My last response to you in that thread was:
.. still, the results are zeros.

Believe you may not be array-entering the formulas correctly ..

In the formula bar, did you see the curly braces { } inserted by Excel
around the formula, viz. it should look like this:
{=SUM(A1:A4+0)}


If you don't see the curlies, then it wasn't confirmed correctly. Re-click
inside the formula bar, press CTRL+SHIFT+ENTER to confirm it, then check for
the curly braces again in the formula bar

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

Hi Max,

I couldn't find that thread this morning for some reason (this is my very
first and only thread here)

Pls read this thread again, I think it better explain what I'm trying to do.

Again the source data is always changing that's why I couldn't add them
(although I still could be able to see the numbers), and the only data
registered a real number is the LAST SET (see explaination above)

thanks for your help

Henry

"Max" wrote:

It's a pity that you seem to have deserted your earlier thread despite the
patience of 2 responders who hung in there with you to try and get you going:

http://tinyurl.com/cry7ba

My last response to you in that thread was:
.. still, the results are zeros.

Believe you may not be array-entering the formulas correctly ..

In the formula bar, did you see the curly braces { } inserted by Excel
around the formula, viz. it should look like this:
{=SUM(A1:A4+0)}


If you don't see the curlies, then it wasn't confirmed correctly. Re-click
inside the formula bar, press CTRL+SHIFT+ENTER to confirm it, then check for
the curly braces again in the formula bar

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

Please keep all discussions within the newsgroup
And never send any file/private email unless it is requested for

Your root prob is that you have numerous** circular reference errors.
Nothing downstream will compute until you remove these circular errors and
re-do your expressions correctly.

**In sheet: SUM, you had this in B2, filled across/down to H4:
=IF(B1='Source data'!$B$7,'Source data'!$C$6, B2)
All of the above are circular ref errors because the expression contains a
reference to the same cell that its placed in. That's a definite no-no. I'm
not sure what you were trying to do in in B2:H4. You could take this up in a
new fresh thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"americasrecycler" wrote:
Hi Max,

I couldn't find that thread this morning for some reason (this is my very
first and only thread here)

Pls read this thread again, I think it better explain what I'm trying to do.

Again the source data is always changing that's why I couldn't add them
(although I still could be able to see the numbers), and the only data
registered a real number is the LAST SET (see explaination above)

thanks for your help

Henry