Categories: Sheets : Ask a "how-to" question :

How to sum a colum, when =SUM(G5:G4471) does not work!

Showing 1-12 of 12 messages
How to sum a colum, when =SUM(G5:G4471) does not work! Gudinnan 1/31/12 5:22 AM
Hello everyone!

Sorry for my easy question, but I´ve tried to solve this little problem for a wile now by searching the internet, but noting helps!

I need to sum a colum after using filter and it´s over 4000 lines. I´ve tried to mark the colum and use sum at the bottom, does´nt work. I tried, as I found on a onther forum, for example, A1-A30, and make A31 =-A30, and sum it all up, but that did´nt work either. I´ve tried to use :, ; and , between the cells name, but nothing works. The sum I get is 0, so what am I doing wrong?

I use chrome and win7.

Sorry if my grammar and spelling is hard to understand. :)

Best regards, Sarah
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Gill 1/31/12 6:55 AM
Are you sure the cells are formatted as numbers?
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Yogi Anand 1/31/12 7:10 AM
Hi Sarah:

Let me add to what Gill has said:

What result do you get when you use the formula ...
=sum(G5:G4471)

How about trying:
=ArrayFormula(sum(iferror(G5:G4471+0))) 

Dos it work for you now? ... if not I suggest you share your spreadsheet and then let us take it from there.

Cheers!
Yogi
Cloud Computing -- Google Docs Way
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Gudinnan 1/31/12 11:25 PM
Thank you for trying to help me!

Yes, the cells are formated as numbers, it is 1 in all of them, so I expected to get a sum around 3000 or more, but less than 4471, because before I use the filter the total amount of lines is 4471 in all of the pages. When I try  =sum(G5:G4471) I get 0, and when trying  =ArrayFormula(sum(iferror(G5:G4471+0))) I get 4473, witch is too much, since it should not be more than 4471 as I tried to explain before.

I really apreciate the help. I will try to solve it by using another computer witch has excel and see if it works better. Otherwise I will come back.

Have a good day all! :)
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Gudinnan 1/31/12 11:41 PM
I am so, so sorry. I discovered when trying in excel, that the format was not numbers, but text in the cells. But how do I see it in google docs? It did´nt come up any ! as it does in excel. 

Best regards, Sarah
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Gudinnan 2/1/12 12:03 AM
Ok, so here comes my problem child. :)

It´s column B that I´ve tried to sum. It should be filtered so only 1 is shown, and in column C it must be only I. I have change all in B to numbers now, not text, but still the sum will not be correct. Even in excel this dosen´t work. At the bottom I´ve put the sum formula that Yogi was nice to help me with.

Let me just say that this is not my work from the begining, I´m just trying to use the information for solving a problem, so I really need to understand what I´m doing wrong, so I don´t do the same misstake.

Thanks in advance! 


anandvarma 2/1/12 12:55 AM <This message has been deleted.>
Re: How to sum a colum, when =SUM(G5:G4471) does not work! anandvarma 2/1/12 12:57 AM
Dear Gudinnan,

The basic structure of SUM function is, it will ignore the text. In your file still column B is a text entry. 
one way of converting text to number is by adding 0
in column D try this formula to convert text to numbers
=b2+0
and drag it down or use array formula
in d2
=ArrayFormula(if(len(B2:B);(B2:B)+0;iferror(1/0))) 

then use Subtotal function
=subtotal(109,d2:d4471)
even if you use the filters it will count the visible cells and gives total

anand varma
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Gudinnan 2/1/12 1:41 AM
Dear Anand,

I tried your sollution but the only thing happening is the sum cell ending up like this: =CONTINUE(D2, 4471, 1)

How can I see that B is a text or number entry? I marked the column and pressed "numbers" in format,
 (its called so in my language, in the tool bar) before I posted the sheet. So it should not be text any more.

Tacks in advance!

Sarah
Re: How to sum a colum, when =SUM(G5:G4471) does not work! anandvarma 2/1/12 2:36 AM
Dear Gudinnan

to identify the contents of cell is text or not

if you look at the cell normally all the text is left justified and numbers are right justified
the formula way is
=ISTEXT(B2) 
if the result is TRUE then the contents of the cell is text.

check this aspect

further i have created the spread sheet at the following link

the formula is in cell e1 


anand varma
Re: How to sum a colum, when =SUM(G5:G4471) does not work! Gudinnan 2/1/12 3:47 AM
Dear Anand,

thank you so much for your time and answers! it really helped me alot!

Hope you have a great day!

Best regards, Sarah
Re: How to sum a colum, when =SUM(G5:G4471) does not work! mark brough 5/7/12 5:02 AM
I too have this problem a lot. (That is, the sum formula often ignores some of the values in the array.) Sometimes it is solved by forcing the format of cells to "number", and sometimes not. Certainly, checking for left-justification or right-justification does not help. The "fix" to add "+0" has worked. But it is poor that Google spreadsheet is unreliable with the SUM formula - sometimes its error is not apparent, and wrong answers are used as though they are correct, and always it slows down the task of completing a large numerical spreadsheet. I have searched the forums, and this bug is not generally known about. Google should warn users more visibly about this bug.