What's going on?!

[basstbone]

Bleh....Really confused and frustrated by the subtotal thing.

I'm trying to subtotal around 400 rows of information that are populated on
a website...so I'm cutting and pasting the information from the website...I
never know what the numbers are going to be in column A.
Number # Of Pkgs # Of Bags
364034 730 32
364034 244 0
364034 6 0
364034 Total 980 32
339037 556 69
339037 Total 556 69
303252 2 0
303252 98 19
303252 Total 100 19
AFTER about line 150 is where the below happens
347322 2 1
347322 142 7
347322 97 3
347322 27 1
347322 26 1
347322 307 12
347322 Total 0?! 0?!
I'm using XL '03 but the computers at work seem to cause the same error.
Is there some kind of *0 problem with the SUM Subtotal function? I just
installed the sp3 patch and looked at the registry but I didn't see the
OPTIONS folder.

Thanx in advance!!

 

[Jerry W. Lewis]

The most common cause for this kind of problem is that the range being summed
only contains text, not numbers. What does =COUNT(range) return?

To sum text that only looks like numbers, you could use
=SUMPRODUCT(range+0), the more robust array formula
=SUM(IF(ISERROR(range+0),"",range+0)), or you could convert the text to
numbers.

To convert the text to numbers, first make sure the cells are not formatted
as "Text", then copy a blank cell and Edit|Paste Special|Add over the range
to be summed.

Jerry

 

[basstbone]

I've tried that =count trick ...shows 96 of 400 are numbers
okay...here's the thing
I've tried formatting the whole colomn like you said below
and I've tried selecting the whole colomn and formatting as numbers with NO
decimals.
and I've tried inserting a colomn...formatting the whole column as numbers
with NO decimals and "special" pasting the values I wanted subtotaled.

The =count trick still shows 96 :*(**
unless I retype the numbers I want subtotaled. And yes I reallllllly need
to use the subtotal function if/when it'll work.

Thanx again in advance

 

[Jerry W. Lewis]

Formatting changes the display, not the value of the cell contents. In
particular, it cannot change a string of text digits into a number. That is
why I suggested that you copy a blank cell and Edit|Paste Special|Add over
the range. That will attempt to coerce the string values into numbers. Did
you try it?

Jerry

 

[basstbone]

Jerry,

Yeah...=count still shows 96 :/

 

[basstbone]

You are right about the formatting problem though the values I rekeyed are
now included in the subtotal. You're 100% right about the numbers stored as
text :/ We just need a fast way to force all of them to be stored as numbers
like your suggestion about special pasting...I'll keep trying but I can't
seem to get it to work now.

 

[basstbone]

Jerry,

I've also tried that special paste trick using formatting numbers with
2 decimals. It only seems to work on certain cells...and then it stops all
the cells are unlocked too.

 

[Jerry W. Lewis]

What is in a cell that does not coerce to a number when zero is added?

Jerry

 

[Rody2003]

To fix this issue insert a new column. Multiply the column which has the data
stored as text by 1. This should give you the exact same data in number
format. Then Copy, past special, value only from the new column into the old
column. Now you should be able to use the subtotal function.

Rody