Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I believe this is a SUMPRODUCT issue | Excel Worksheet Functions | |||
SumProduct CountIF issue | Excel Worksheet Functions | |||
Sumproduct #N/A! error issue | Excel Worksheet Functions | |||
Issue with sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT issue | Excel Worksheet Functions |