SUMIF function does not recognize Numeric Value in Array

[Adam]

I have a simple (but large) array, including the fields
Category, Name (both text fields) and several Statistics
(Numeric Values). This array is updated weekly from a
data source -We simply copy/paste the update into our
workbook.

To summarize this array, we have set up a table to group
by categories, and then use a SUMIF function to look up
the Name in the array, and report specific stats back.

The Problem: The SUMIF function reports back "0" instead
of the appropriate numeric value for 1 specific field.
(Formula works for other fields). If I go down and retype
the numbers in the field, the SUMIF now works and shows
the correct value.

I've had this problem w. Excel before, where a "number" is
not recognized unless you retype it. It is not feasible
to go through the entire array and retype the values in
this field. (Note - I have tried to replace via a Paste
Special/Value, but the numbers are still not recognized.)

Any ideas/solutions would be appreciated.
Thanks,
A

 

[Frank Kabel]

Hi
the numbers are due to the import procedure stored as text. Try the
following:
- format this column as 'General' or 'Number'
- select an empty cell
- copy this cell
- select your numbers and goto 'Edit - Paste Special'. Choose 'Add'

 

[Ron Rosenfeld]

I have a simple (but large) array, including the fields
Category, Name (both text fields) and several Statistics
(Numeric Values). This array is updated weekly from a
data source -We simply copy/paste the update into our
workbook.

To summarize this array, we have set up a table to group
by categories, and then use a SUMIF function to look up
the Name in the array, and report specific stats back.

The Problem: The SUMIF function reports back "0" instead
of the appropriate numeric value for 1 specific field.
(Formula works for other fields). If I go down and retype
the numbers in the field, the SUMIF now works and shows
the correct value.

I've had this problem w. Excel before, where a "number" is
not recognized unless you retype it. It is not feasible
to go through the entire array and retype the values in
this field. (Note - I have tried to replace via a Paste
Special/Value, but the numbers are still not recognized.)

Any ideas/solutions would be appreciated.
Thanks,
A

Most likely your data source has these values as TEXT. One way that might work
to convert them would be to:
Select a blank cell
Copy
Select the errant column
Paste Special Operation Add

If that doesn't work, then the data will have to be cleaned up better. For
example, if your data is coming from an html document, or from the web, there
may be a CHAR(160) in the field that would have to be explicitly removed.


--ron