Sum errors in Rows, columns and ranges

[Wes at CCC]

I have a spreadsheet where some rows, some columns and some ranges are not
summed correctly and values counted correctly. The functions involved are SUM
and COUNTIF.

I have been using Excel extensively since the early '90s. I am not a new
user. I currently have Office XP.

I will send you the spreadsheet if I knew where to send it.

Thanks.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...c5a815f&dg=microsoft.public.excel.crashesgpfs

 

[Nick Hodge]

Undoubtedly, these 'numbers' are actually text. This normally happens when
either

1) The data is imported or cut/pasted from another program
2) Formatted in Excel as text and then formatted back to a number (text
formatting is a one-way street)

To correct it, either, enter a 1 in a blank cell and copy it. Highlight all
your 'numbers' and edit>paste special>values+multiply. equally you can copy
a blank cell and use add rather than multiply

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Wes at CCC]

Hi Nick,

I had not considered that the values could be text. However, if that were
the case the sums would be less than the expected value. In the case I have,
the sums are MORE than the expected value: expected 45, actual 89; expected
45, actual 133.

What I think caused this is that I copied and pasted a merged cell on top of
a 3x3 range without first deleting the contents of the 3x3 range. I think
that in the copy/paste operation some of the values pasted over were not
deleted. That is, the underlying cells were not deleted during the paste
process and are adding to both the sum and the counts.

My analysis of the situation could be totally wrong, but the spreadsheet's
behavior seems to support it.

I think this is a problem.

Wes

 

[Nick Hodge]

mmmm Merged Cells...more than possible!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)