Instead of having to work-around the junk that one gets when importing data
from a website why not clean that junk and get rid of it?
There is a macro here that will do just that:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
I have this macro attached to a button on one of my toolbars. Every time I
import/copy/paste from a website I run this macro to clean all the junk.
--
Biff
Microsoft Excel MVP
"Jeff Gross" wrote in message
...
I have a spreadsheet in which I need to sum a column based on two criteria.
The data is a MS query from an external file that is retrieved from a
website. The data always has two hidden spaces at the end of each cell.
The formula must look for the word "APPR " in column BF35:BF1000 and
"ANKLE
" in column BG35:BG1000. If those criteria are met, then the associated
data in column BA35:BA1000 must be added together (not counted as in using
the "Count" statement).
At first I used a sumproduct formula:
=SUMPRODUCT((BF35:BF1000="APPR ")*(BG35:BG1000="ANKLE ")*(BA35:BA1000))
but it looks like the data in column BA35:BA1000 may be text and not a
number (the cell is in a general format), so it looks like the SUM
statement
is ignoring them.
Does anyone have any ideas on how to get this summation to occur?
Thanks in advance.
Jeff