#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 64
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 64
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 64
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I believe this is a SUMPRODUCT issue WLMPilot Excel Worksheet Functions 2 August 14th 08 09:35 PM
SumProduct CountIF issue JimG Excel Worksheet Functions 5 August 3rd 08 06:20 AM
Sumproduct #N/A! error issue A. Gallardo Excel Worksheet Functions 2 October 26th 05 04:00 PM
Issue with sumproduct Steved Excel Worksheet Functions 8 July 18th 05 11:19 PM
SUMPRODUCT issue TonyL Excel Worksheet Functions 2 May 27th 05 12:46 AM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"