Thread
:
Sumproduct Issue
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
Jeff Gross
external usenet poster
Posts: 64
Sumproduct Issue
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" wrote:
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:B G1000)="ANKLE"))
If still a problem then you need to fix the numbers to be numbers. Let us
know.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"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
Reply With Quote
Jeff Gross
View Public Profile
Find all posts by Jeff Gross