Sumproduct Issue

[Jeff Gross]

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

 

[T. Valko]

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.

 

[Don Guillett]

Is the count part of the formula working as written
SUMPRODUCT((BF35:BF1000="APPR ")*(BG35:BG1000="ANKLE "))
If not, you may want to use TRIM
SUMPRODUCT((TRIM(BF35:BF1000)="APPR")*(TRIM(BG35:BG1000)="ANKLE"))

If still a problem then you need to fix the numbers to be numbers. Let us
know.

 

[Jeff Gross]

Thanks for the feedback. I'm modifying this file which is maintained weekly
by another employee at my client's office. I had thought about putting some
backend VBS in to deal with the issue but thought I could work around it for
this particular project.

Jeff

 

[Jeff Gross]

Thanks. Column BA is where the data is that has to be summed. I had a
thought and it seems to have worked. I went into my MS query and forced the
data that is ultimately in Column BA to appear as a number and now my formula
is working.

Go figure. Thanks for the quick response.

Jeff

 

[Don Guillett]

Good to hear. You still may want to TRIM.