Sumproduct Issue
 

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

Sumproduct Issue
 

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

Sumproduct Issue
 

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

Sumproduct Issue
 

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

"T. Valko" wrote:

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

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

Sumproduct Issue
 

Good to hear. You still may want to TRIM.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff Gross" wrote in message
...
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