Re: Sumproduct Issue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <JeffGross@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E71BF19D-D105-4F6E-9EE3-61AB7754A20B@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Hard Disk Activity
    ... (The whole PCHealth / Windows File Protection ... junk services culled. ... actually accessing the disk, though. ... If you want to clean up and disable some of the ...
    (microsoft.public.vb.general.discussion)
  • Re: Restore/reinstall
    ... I like to reformat from time to time but geesh I hate having to put all the junk programs back on. ... Now clean up your system. ... Its kinda what your vendor did on that restore partition. ... The first time you do an image, you can write it to your C: drive and then hopefully you can burn it to a DVD and save it. ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Restore/reinstall
    ... I like to reformat from time to time but geesh I hate having to put all the junk programs back on. ... Now clean up your system. ... Its kinda what your vendor did on that restore partition. ... The first time you do an image, you can write it to your C: drive and then hopefully you can burn it to a DVD and save it. ...
    (microsoft.public.windowsxp.help_and_support)
  • Re: Cancelling active print job -- it just doesnt stop!
    ... purpose at some time. ... some program fails or is exited uncleanly, leaving the junk. ... You could have things set up to clean out /tmp late at night, ...
    (comp.os.linux.misc)
  • Re: Am I the only one here who doesnt use RSS? (OT - What websites do you visit daily/weekly?)
    ... It's a way of scraping a website at regular intervals looking for new ... News site has a feed link and it's a good place to start if you want to ... the Royal Mail post a lot of junk through my door I don't want. ...
    (uk.media.tv.misc)