SUM of a row of formulas

[americasrecycler]

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]

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

 

[Max]

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]

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]

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]

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]

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

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]

A couple of more things to test.

Try
=count(a1:d1)
(any troublesome row)
to see what is returned
=max(a1:d1)
=min(a1:d1)

Check the numberformat. Make sure it's General and not hiding anything.

Widen the column to see if the decimal places are being suppressed.

 

[americasrecycler]

Dave,

I got all Zeros for "count"," min" and "max" (A11).... the source values
are the result of calculation formulas, I have tested all of the source
numbers, and all of them are TRUE.

A couple of more things to test.

Try
=count(a1:d1)
(any troublesome row)
to see what is returned
=max(a1:d1)
=min(a1:d1)

Check the numberformat. Make sure it's General and not hiding anything.

Widen the column to see if the decimal places are being suppressed.

 

[americasrecycler]

Hi Max,

still, the results are zeros.

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

 

[Dave Peterson]

If you get 0's for this row, then this row is not being included in
=sum(bycolumn) formulas.

Do you get the same results if you use the same formulas in all the rows? I'd
bet not. I bet some are ok.

And if that's true, then you're going to have to find those offending cells that
are really text.

But you haven't posted your real formula for one of the offending cells yet.

Dave,

I got all Zeros for "count"," min" and "max" (A11).... the source values
are the result of calculation formulas, I have tested all of the source
numbers, and all of them are TRUE.

 

[americasrecycler]

Here's the real formula

=IF('Good Units'!$B$41=C3,'Good Units'!$D$41,C4)

which "Good Units"!D41 = math calculation

If you get 0's for this row, then this row is not being included in
=sum(bycolumn) formulas.

Do you get the same results if you use the same formulas in all the rows? I'd
bet not. I bet some are ok.

And if that's true, then you're going to have to find those offending cells that
are really text.

But you haven't posted your real formula for one of the offending cells yet.

 

[Dave Peterson]

Check to see if D41 on Good Units is a number.
Check to see if C4 on the sheet with the formula is a number.

What's the formula in D41 of Good Units?
What's the formula in C4 of the sheet with the formula?

You have that vertical sum like:
=sum(a1:a99)
what do you see when you use:
=count(a1:a99)
and
=counta(a1:a99)
(avoid any header rows when you type in the range)

Here's the real formula

=IF('Good Units'!$B$41=C3,'Good Units'!$D$41,C4)

which "Good Units"!D41 = math calculation

 

[Max]

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