On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

I would go back to the "sending" cells and verify that they're really
numbers--not just text that look like numbers.

You could use a formula like:
=isnumber(c4)
to check any single cell.

If you see False, then you can reformat the cell as General (or anything but
Text) and then reenter the value or formula (Hit F2, then enter).

americasrecycler wrote:

On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

--

Dave Peterson

Hi Dave and Max,

thanks for your help but I still couldn't get any result. Here's what I
have, hope better explain.

A B C D E F G H
1 2 3 0 2
2 5 6 0 5
3 7 9 0

A1=IF('sheet1'!$B$41=H1,'Sheet1'!$D$41,A1)
note A2, B1 and B2 have similar formula.

A3 =sum(A1:A2) , B3 =sum(B1:B2)

C1 =sum(A1:B1) , C2 =sum(A2:B2) ...... GOT ZEROS INSTEAD OF 5 AND 11
RESPECTIVELY.

NOTE: ISNUMBER(A1) = 0 ........ INSTEAD OF 5 ... SO IS A2, B1, AND B2 ....

THANKS IN ADVANCE

"Dave Peterson" wrote:

I would go back to the "sending" cells and verify that they're really
numbers--not just text that look like numbers.

You could use a formula like:
=isnumber(c4)
to check any single cell.

If you see False, then you can reformat the cell as General (or anything but
Text) and then reenter the value or formula (Hit F2, then enter).

americasrecycler wrote:

On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

--

Dave Peterson

That means that your data that looks like numbers aren't really numbers.

You'll want to convert them to numbers.

If there are only a few, then use the technique I suggested in the previous
post. Do this in the worksheet that contains the data Sheet1.

But I don't trust the formula you posted.
=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4)
That's not the way excel would show it.

So it may be a good idea to copy the real formula from the formula bar and
include it in your next followup message if you can't get it to work.

And as an aside, if
=isnumber(a1)
is returning 1, then I'd recommend that you turn off all the Lotus transition
settings:
Tools|Options|Transition tab
(xl2003 menus)

americasrecycler wrote:

Hi Dave and Max,

thanks for your help but I still couldn't get any result. Here's what I
have, hope better explain.

A B C D E F G H
1 2 3 0 2
2 5 6 0 5
3 7 9 0

A1=IF('sheet1'!$B$41=H1,'Sheet1'!$D$41,A1)
note A2, B1 and B2 have similar formula.

A3 =sum(A1:A2) , B3 =sum(B1:B2)

C1 =sum(A1:B1) , C2 =sum(A2:B2) ...... GOT ZEROS INSTEAD OF 5 AND 11
RESPECTIVELY.

NOTE: ISNUMBER(A1) = 0 ........ INSTEAD OF 5 ... SO IS A2, B1, AND B2 ....

THANKS IN ADVANCE

"Dave Peterson" wrote:

I would go back to the "sending" cells and verify that they're really
numbers--not just text that look like numbers.

You could use a formula like:
=isnumber(c4)
to check any single cell.

If you see False, then you can reformat the cell as General (or anything but
Text) and then reenter the value or formula (Hit F2, then enter).

americasrecycler wrote:

On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

--

Dave Peterson


--

Dave Peterson

HI DAVE,

The information I posted is just an example of what I'm doing, the data base
is much much larger.

I'm still confused, that I could be able to sum for ea column (A,B,C) , but
couldn't sum across (A+B+C).

If the numbers aren't real, I shouldn't be able to do neither right?

"Dave Peterson" wrote:

That means that your data that looks like numbers aren't really numbers.

You'll want to convert them to numbers.

If there are only a few, then use the technique I suggested in the previous
post. Do this in the worksheet that contains the data Sheet1.

But I don't trust the formula you posted.
=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4)
That's not the way excel would show it.

So it may be a good idea to copy the real formula from the formula bar and
include it in your next followup message if you can't get it to work.

And as an aside, if
=isnumber(a1)
is returning 1, then I'd recommend that you turn off all the Lotus transition
settings:
Tools|Options|Transition tab
(xl2003 menus)

americasrecycler wrote:

Hi Dave and Max,

thanks for your help but I still couldn't get any result. Here's what I
have, hope better explain.

A B C D E F G H
1 2 3 0 2
2 5 6 0 5
3 7 9 0

A1=IF('sheet1'!$B$41=H1,'Sheet1'!$D$41,A1)
note A2, B1 and B2 have similar formula.

A3 =sum(A1:A2) , B3 =sum(B1:B2)

C1 =sum(A1:B1) , C2 =sum(A2:B2) ...... GOT ZEROS INSTEAD OF 5 AND 11
RESPECTIVELY.

NOTE: ISNUMBER(A1) = 0 ........ INSTEAD OF 5 ... SO IS A2, B1, AND B2 ....

THANKS IN ADVANCE

"Dave Peterson" wrote:

I would go back to the "sending" cells and verify that they're really
numbers--not just text that look like numbers.

You could use a formula like:
=isnumber(c4)
to check any single cell.

If you see False, then you can reformat the cell as General (or anything but
Text) and then reenter the value or formula (Hit F2, then enter).

americasrecycler wrote:

On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

--

Dave Peterson


--

Dave Peterson

I didn't notice your earlier point that you could use =sum(column) and get a
non-zero result.

Mayby you have calculation set to manual and it just hasn't recalculated???

I don't have any other guess (and this one doesn't sound like it could be the
solution).

americasrecycler wrote:

HI DAVE,

The information I posted is just an example of what I'm doing, the data base
is much much larger.

I'm still confused, that I could be able to sum for ea column (A,B,C) , but
couldn't sum across (A+B+C).

If the numbers aren't real, I shouldn't be able to do neither right?

"Dave Peterson" wrote:

That means that your data that looks like numbers aren't really numbers.

You'll want to convert them to numbers.

If there are only a few, then use the technique I suggested in the previous
post. Do this in the worksheet that contains the data Sheet1.

But I don't trust the formula you posted.
=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4)
That's not the way excel would show it.

So it may be a good idea to copy the real formula from the formula bar and
include it in your next followup message if you can't get it to work.

And as an aside, if
=isnumber(a1)
is returning 1, then I'd recommend that you turn off all the Lotus transition
settings:
Tools|Options|Transition tab
(xl2003 menus)

americasrecycler wrote:

Hi Dave and Max,

thanks for your help but I still couldn't get any result. Here's what I
have, hope better explain.

A B C D E F G H
1 2 3 0 2
2 5 6 0 5
3 7 9 0

A1=IF('sheet1'!$B$41=H1,'Sheet1'!$D$41,A1)
note A2, B1 and B2 have similar formula.

A3 =sum(A1:A2) , B3 =sum(B1:B2)

C1 =sum(A1:B1) , C2 =sum(A2:B2) ...... GOT ZEROS INSTEAD OF 5 AND 11
RESPECTIVELY.

NOTE: ISNUMBER(A1) = 0 ........ INSTEAD OF 5 ... SO IS A2, B1, AND B2 ....

THANKS IN ADVANCE

"Dave Peterson" wrote:

I would go back to the "sending" cells and verify that they're really
numbers--not just text that look like numbers.

You could use a formula like:
=isnumber(c4)
to check any single cell.

If you see False, then you can reformat the cell as General (or anything but
Text) and then reenter the value or formula (Hit F2, then enter).

americasrecycler wrote:

On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

One thought, your prob with the zeros might be text nums in the source ranges

Put this slightly revised formula in A5, then array-enter the formula, ie
press CTRL+SHIFT+ENTER to confirm it:
=SUM(A1:A4+0)
Copy across as required. The "+0" will coerce any text nums within the range
to real nums for correct summations.

If it helped, pl press the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"americasrecycler" wrote:
On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

Hi Max,

still, the results are zeros.

"Max" wrote:

One thought, your prob with the zeros might be text nums in the source ranges

Put this slightly revised formula in A5, then array-enter the formula, ie
press CTRL+SHIFT+ENTER to confirm it:
=SUM(A1:A4+0)
Copy across as required. The "+0" will coerce any text nums within the range
to real nums for correct summations.

If it helped, pl press the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"americasrecycler" wrote:
On sheet2 of my workbook:

A B C D E F G
1
2
3
4

A1=IF('Good Units'!$B$41=C3,'Sheet1'!$D$41,C4) similar formulas for
B1,C1,D1,E1,F1(to B4,C4,D4,F4)

Now on A4 (to F4), I could be able to get the result using = sum(A1:A4) ,
but on G1(to G4), I only got "0" from =sum(A1:F1)

In another word, I only get the sum for each column, but I coudn't get the
sum for the accross the rows.

Thanks in advance

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