Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with three arrays
Hello and thank you in advance for your assistance.
I have a spreadsheet as follows: Column C= Revenue Column D= Units Column E = Months, as a number (i.e. 1-12) Column F = Status, expressed as either Prospect or Definite Column G = Year (i.e. 2009) Column A,B are labels and not used in the calculations. I want to sum the units and revenue based on year, month, and status (i.e., how many units sold in January of 2009 that are in Prospect status). The result for unit and revenue are in two other columns, K & L (i.e. there is a summary cell for unit based on month, year and status, and another for revenue based on month, year, and status) The formula I am using is: =SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),D1:D500) for units, and =SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),C1:C500) for revenue Excel returns 0, which I know is incorrect. I have tried removing the quotes from the year and the month, but still get 0. I have checked the cell format for the result cell and it is set to number format. Thanks again for your thoughts. Best regards, Alberto |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with three arrays
Definitely leave the quotes off on the year and month and make sure numbers
are numbers and not stored as text. Even if you set a number-stored-as-text to number format, it may still be read as text by Excel. Try copying a blank cell and using paste special to subtract from all of the cells in your year and month ranges. Also, use absolute references on your ranges if the formula is going to move at all. Finally, in your data make sure there are no leading or trailing spaces (or characters that look like spaces) in the text columns by editing the entry in the formula bar and selecting the entire contents. =SUMPRODUCT(--($G$1:$G$500=2009),--($F$1:$F$500="Prospect"),--($E$1:$E$500=1),$D$1:$D$500) =SUMPRODUCT(--($G$1:$G$500=2009),--($F$1:$F$500="Prospect"),--($E$1:$E$500=1),$C$1:$C$500) If none of the above work, try hitting F9 to see if your workbook is on manual calculation. And if none of that works, open a blank sheet and do the following: Highlight 500 cells and in the first cell type =Sheet1!$G$1:$G$500=2009 then enter it using control+shift+enter (this assumes your sheet name is sheet1, change that if you need to). Repeat this for ranges F and E. This will show you exactly how the contents of each array are evaluating. If something is not a match and you know it should be, then you know something is wrong with the data entered in that cell. Copy the entire contents of the offending cell, push CTRL+H, paste it into the find field and replace with a clean entry that you know is correct. "Alcala" wrote: Hello and thank you in advance for your assistance. I have a spreadsheet as follows: Column C= Revenue Column D= Units Column E = Months, as a number (i.e. 1-12) Column F = Status, expressed as either Prospect or Definite Column G = Year (i.e. 2009) Column A,B are labels and not used in the calculations. I want to sum the units and revenue based on year, month, and status (i.e., how many units sold in January of 2009 that are in Prospect status). The result for unit and revenue are in two other columns, K & L (i.e. there is a summary cell for unit based on month, year and status, and another for revenue based on month, year, and status) The formula I am using is: =SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),D1:D500) for units, and =SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),C1:C500) for revenue Excel returns 0, which I know is incorrect. I have tried removing the quotes from the year and the month, but still get 0. I have checked the cell format for the result cell and it is set to number format. Thanks again for your thoughts. Best regards, Alberto |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct with three arrays
Thanks! It is working now, and I suspect it is the absolute reference and
removing the "" that did the trick as the formulas are now returning confirmed numbers and I did not do any of the other steps. Thanks again for your assistance, and have a great weekend!. Regards, Alberto "~L" wrote: Definitely leave the quotes off on the year and month and make sure numbers are numbers and not stored as text. Even if you set a number-stored-as-text to number format, it may still be read as text by Excel. Try copying a blank cell and using paste special to subtract from all of the cells in your year and month ranges. Also, use absolute references on your ranges if the formula is going to move at all. Finally, in your data make sure there are no leading or trailing spaces (or characters that look like spaces) in the text columns by editing the entry in the formula bar and selecting the entire contents. =SUMPRODUCT(--($G$1:$G$500=2009),--($F$1:$F$500="Prospect"),--($E$1:$E$500=1),$D$1:$D$500) =SUMPRODUCT(--($G$1:$G$500=2009),--($F$1:$F$500="Prospect"),--($E$1:$E$500=1),$C$1:$C$500) If none of the above work, try hitting F9 to see if your workbook is on manual calculation. And if none of that works, open a blank sheet and do the following: Highlight 500 cells and in the first cell type =Sheet1!$G$1:$G$500=2009 then enter it using control+shift+enter (this assumes your sheet name is sheet1, change that if you need to). Repeat this for ranges F and E. This will show you exactly how the contents of each array are evaluating. If something is not a match and you know it should be, then you know something is wrong with the data entered in that cell. Copy the entire contents of the offending cell, push CTRL+H, paste it into the find field and replace with a clean entry that you know is correct. "Alcala" wrote: Hello and thank you in advance for your assistance. I have a spreadsheet as follows: Column C= Revenue Column D= Units Column E = Months, as a number (i.e. 1-12) Column F = Status, expressed as either Prospect or Definite Column G = Year (i.e. 2009) Column A,B are labels and not used in the calculations. I want to sum the units and revenue based on year, month, and status (i.e., how many units sold in January of 2009 that are in Prospect status). The result for unit and revenue are in two other columns, K & L (i.e. there is a summary cell for unit based on month, year and status, and another for revenue based on month, year, and status) The formula I am using is: =SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),D1:D500) for units, and =SUMPRODUCT(--(G1:G500="2009"),--(F1:F500="Prospect"),--(E1:E500="1"),C1:C500) for revenue Excel returns 0, which I know is incorrect. I have tried removing the quotes from the year and the month, but still get 0. I have checked the cell format for the result cell and it is set to number format. Thanks again for your thoughts. Best regards, Alberto |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional arrays within SUMPRODUCT | Excel Worksheet Functions | |||
SUMPRODUCT where arrays have different dimensions | Excel Discussion (Misc queries) | |||
Using SUMPRODUCT with arrays | Excel Discussion (Misc queries) | |||
SUMPRODUCT with 3 arrays not working | Excel Worksheet Functions | |||
Sumproduct arrays | Excel Discussion (Misc queries) |