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!