Sumproduct Issue

  • Thread starter Thread starter Jeff Gross
  • Start date Start date
J

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
 
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.
 
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.
 
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
 
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
 
Back
Top