Random #VALUE! error in simple SUM formula results

[Greg in CO]

Hi!

I have admins that are pasting numbers from one worksheet into cells on
another sheet. Some of the cells are blank, others have a zero value. They
are doing Copy>Paste Special.

In the target worksheet, the cells they are pasting into are summed in a
column to create a total.

In some cases, the sum works fine, whether numbers, zero values, or blank
cells; in other cases, I get a #VALUE! error. When I go an audit the row
being summed, I may find several blank cells. I will add in a zero to the
blank cells and eventually, the error will go away and I will get a sum.
What is odd is that if I have, for example 8 cells, with only 3 having
numbers and 5 being blank, I may only have to add a zero into 1 or two cells
befoire the error disappers. There are still blank cells in the sum range.

Ideas?

Thanks!

 

[Bernard Liengme]

Something is wrong here. Sum does not care about blank cells. But 1 in H1
and 1 in H400 and use =SUM(H1:H400) and you get 2.

Look carefully at the 'blank' cells. What do you see in the Formula Bar
It is very hard to upset SUM. It A1 =1 and A2 to A4 hold the text values:
cat, dog, mouse , and A5 holds 5, then SUM(A1:A5) gives 6

Tell us what you find. Maybe you could send me a sample file (remove
TRUENORTH. to get my real email)
best wishes

 

[Greg in CO]

Hi Bernard!

My thoughts exactly - it is hard to make SUM unhappy. We have looked in the
originating cells - these are cells where the end-users type in a number -
some people enter a zero, others just leave a blank. When the admins do
Copy>Paste Special, all they get are the Values of number or blanks.

In reviewing cells where there are blanks, the formula bar is blank. I then
enter a zero and the #VALUE! error goes away...in other blank cells I enter a
zero and the error remains.

I have even tried doing the copy>paste special myself - same random results:

Cell A1 has a Sum formula for Cells A2-A10. There is a #VALUE! error in A1.
A2 has a 2, A3 has a 5, A4 has a 5, A6 - 0, A7 - 0, A8 - blank, A9 - blank,
A10 - 0. The sum should be 12. I'll go in and enter a zero in A8, still get
the error; enter a zero in A9, error goes away. I go back and delete the
zero in A8, making it blank, no error.

The error is random. Could it have something to do with the end-users'
Excel not having the correct updates? It does not happen all the time with
info from the same end-users. Completely random.

It is also hard to replicate.

Thanks!

 

[Ronmac]

I have a similar problem to this, but the solution here is too complicated for me (I'm a complete newbie).


I have created a workbook with several worksheets. Each worksheet is named after a department. The last worksheet is called "TOTALS" and contains two columns (Dept Name, Total). The department names are listed in Column A, from row 2 downwards.

I manually inserted "=" in the "Total" column for each department, then switched to that department's worksheet, selected the autosum figure there, and hit return. This updated the figure on the Totals worksheet. The autosum is created automatically (a line in my code) but I cannot figure a way of having the macro pick up that sum and transfer it to the Totals page on the correct department row.

I tried recording a macro but got the #VALUE! error message.

I am happy to post the code or the workbook if it would help you guys to help me!

Regards,

Ron