Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
I hope someone can help my colleague with this one. He has a spreadsheet in
Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A Col B Col C Col D Col E Date Site Cases Plts Tonnes 1/1/08 060 25 5 2000 1/1/08 061 50 10 3900 etc for approx 20 further sites 2/1/08 060 32 8 3000 2/1/08 061 45 9 3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
Why not just return all three values in separate cells, one for Cases,
one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29*am, Ellie wrote: I hope someone can help my colleague with this one. *He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A * * Col B * * *Col C * * *Col D * * *Col E * * * Date * * *Site * * * *Cases * * Plts * * * *Tonnes 1/1/08 * 060 * * * * *25 * * * * *5 * * * * * *2000 1/1/08 * 061 * * * * *50 * * * * 10 * * * * * 3900 etc for approx 20 further sites 2/1/08 * 060 * * * * *32 * * * * *8 * * * * * *3000 2/1/08 * 061 * * * * *45 * * * * *9 * * * * * *3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. *The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
Hi Pete
Thanks for the reply. We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A Col B Col C Col D Col E Date Site Cases Plts Tonnes 1/1/08 060 25 5 2000 1/1/08 061 50 10 3900 etc for approx 20 further sites 2/1/08 060 32 8 3000 2/1/08 061 45 9 3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
The #VALUE error implies that there is probably something wrong with
your data - it might look like numbers or dates but is in fact text. You or your colleague will need to check the data thoroughly. Also, you can't use a full column reference with SP, so if you post the formula you have used here, then we might be able to comment further on it. Pete On Feb 7, 12:12*pm, Ellie wrote: Hi Pete Thanks for the reply. *We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. * "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. *He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A * * Col B * * *Col C * * *Col D * * *Col E * * * Date * * *Site * * * *Cases * * Plts * * * *Tonnes 1/1/08 * 060 * * * * *25 * * * * *5 * * * * * *2000 1/1/08 * 061 * * * * *50 * * * * 10 * * * * * 3900 etc for approx 20 further sites 2/1/08 * 060 * * * * *32 * * * * *8 * * * * * *3000 2/1/08 * 061 * * * * *45 * * * * *9 * * * * * *3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. *The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
Hi Pete
The #VALUE error came from my colleague having selected 3 columns originally. Value totals are still 0. Here is a copy of the formula used:- =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26=$J$17),--($B$2:$B$26=$H$19),--($C$2:$C$26=I18)) Just as a trial, columns H, I and J my colleague is using as a test area on the same sheet for the manual entries. I17 = start date of choice, J17 = end date of choice, H19 = site number of choice and I18, J18 and K18 = respective columns for population of totals for cases, pallets and tonnes. Many thanks for your help. Ellie "Pete_UK" wrote: The #VALUE error implies that there is probably something wrong with your data - it might look like numbers or dates but is in fact text. You or your colleague will need to check the data thoroughly. Also, you can't use a full column reference with SP, so if you post the formula you have used here, then we might be able to comment further on it. Pete On Feb 7, 12:12 pm, Ellie wrote: Hi Pete Thanks for the reply. We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A Col B Col C Col D Col E Date Site Cases Plts Tonnes 1/1/08 060 25 5 2000 1/1/08 061 50 10 3900 etc for approx 20 further sites 2/1/08 060 32 8 3000 2/1/08 061 45 9 3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
I would suggest the following, then:
=SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),*($C$2:$C$26)) this will give you the totals of column C where the cells meet the criteria. Note that you want to include dates that are greater than or equal to the start date, less than or equal to the end date. Similar formulae: =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),*($D$2:$D$26)) will give you total of pallets (i.e. column D in formula) =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),*($E$2:$E$26)) will give you a total of the Tonnes column. Put these in adjacent cells - presumably I18, J18 and K18. Hope this helps. Pete On Feb 7, 1:09*pm, Ellie wrote: Hi Pete The #VALUE error came from my colleague having selected 3 columns originally. *Value totals are still 0. *Here is a copy of the formula used:- =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26=$J$17),--($B$2:$B$26=$H$19),*--($C$2:$C$26=I18)) Just as a trial, columns H, I and J my colleague is using as a test area on the same sheet for the manual entries. *I17 = start date of choice, J17 = end date of choice, H19 = site number of choice and I18, J18 and K18 = respective columns for population of totals for cases, pallets and tonnes. Many thanks for your help. Ellie "Pete_UK" wrote: The #VALUE error implies that there is probably something wrong with your data - it might look like numbers or dates but is in fact text. You or your colleague will need to check the data thoroughly. Also, you can't use a full column reference with SP, so if you post the formula you have used here, then we might be able to comment further on it. Pete On Feb 7, 12:12 pm, Ellie wrote: Hi Pete Thanks for the reply. *We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. * "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. *He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A * * Col B * * *Col C * * *Col D * * *Col E * * * Date * * *Site * * * *Cases * * Plts * * * *Tonnes 1/1/08 * 060 * * * * *25 * * * * *5 * * * * * *2000 1/1/08 * 061 * * * * *50 * * * * 10 * * * * * 3900 etc for approx 20 further sites 2/1/08 * 060 * * * * *32 * * * * *8 * * * * * *3000 2/1/08 * 061 * * * * *45 * * * * *9 * * * * * *3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. *The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
Unless I17 and J17 have identical values, I would expect an output of zero
from your formula, as otherwise you can't satisfy the first and second conditions together. -- David Biddulph "Ellie" wrote in message ... Hi Pete The #VALUE error came from my colleague having selected 3 columns originally. Value totals are still 0. Here is a copy of the formula used:- =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26=$J$17),--($B$2:$B$26=$H$19),--($C$2:$C$26=I18)) Just as a trial, columns H, I and J my colleague is using as a test area on the same sheet for the manual entries. I17 = start date of choice, J17 = end date of choice, H19 = site number of choice and I18, J18 and K18 = respective columns for population of totals for cases, pallets and tonnes. Many thanks for your help. Ellie "Pete_UK" wrote: The #VALUE error implies that there is probably something wrong with your data - it might look like numbers or dates but is in fact text. You or your colleague will need to check the data thoroughly. Also, you can't use a full column reference with SP, so if you post the formula you have used here, then we might be able to comment further on it. Pete On Feb 7, 12:12 pm, Ellie wrote: Hi Pete Thanks for the reply. We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A Col B Col C Col D Col E Date Site Cases Plts Tonnes 1/1/08 060 25 5 2000 1/1/08 061 50 10 3900 etc for approx 20 further sites 2/1/08 060 32 8 3000 2/1/08 061 45 9 3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
It does help and thank you so much for your help and time.
Ellie "Pete_UK" wrote: I would suggest the following, then: =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),Â*($C$2:$C$26)) this will give you the totals of column C where the cells meet the criteria. Note that you want to include dates that are greater than or equal to the start date, less than or equal to the end date. Similar formulae: =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),Â*($D$2:$D$26)) will give you total of pallets (i.e. column D in formula) =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),Â*($E$2:$E$26)) will give you a total of the Tonnes column. Put these in adjacent cells - presumably I18, J18 and K18. Hope this helps. Pete On Feb 7, 1:09 pm, Ellie wrote: Hi Pete The #VALUE error came from my colleague having selected 3 columns originally. Value totals are still 0. Here is a copy of the formula used:- =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26=$J$17),--($B$2:$B$26=$H$19),Â*--($C$2:$C$26=I18)) Just as a trial, columns H, I and J my colleague is using as a test area on the same sheet for the manual entries. I17 = start date of choice, J17 = end date of choice, H19 = site number of choice and I18, J18 and K18 = respective columns for population of totals for cases, pallets and tonnes. Many thanks for your help. Ellie "Pete_UK" wrote: The #VALUE error implies that there is probably something wrong with your data - it might look like numbers or dates but is in fact text. You or your colleague will need to check the data thoroughly. Also, you can't use a full column reference with SP, so if you post the formula you have used here, then we might be able to comment further on it. Pete On Feb 7, 12:12 pm, Ellie wrote: Hi Pete Thanks for the reply. We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A Col B Col C Col D Col E Date Site Cases Plts Tonnes 1/1/08 060 25 5 2000 1/1/08 061 50 10 3900 etc for approx 20 further sites 2/1/08 060 32 8 3000 2/1/08 061 45 9 3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT help
Glad to hear that, Ellie - thanks for feeding back.
Pete On Feb 7, 2:13*pm, Ellie wrote: It does help and thank you so much for your help and time. Ellie "Pete_UK" wrote: I would suggest the following, then: =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),*($C$2:$C$26)) this will give you the totals of column C where the cells meet the criteria. Note that you want to include dates that are greater than or equal to the start date, less than or equal to the end date. Similar formulae: =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),*($D$2:$D$26)) will give you total of pallets (i.e. column D in formula) =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26= $H$19),*($E$2:$E$26)) will give you a total of the Tonnes column. Put these in adjacent cells - presumably I18, J18 and K18. Hope this helps. Pete On Feb 7, 1:09 pm, Ellie wrote: Hi Pete The #VALUE error came from my colleague having selected 3 columns originally. *Value totals are still 0. *Here is a copy of the formula used:- =SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26=$J$17),--($B$2:$B$26=$H$19),**--($C$2:$C$26=I18)) Just as a trial, columns H, I and J my colleague is using as a test area on the same sheet for the manual entries. *I17 = start date of choice, J17 = end date of choice, H19 = site number of choice and I18, J18 and K18 = respective columns for population of totals for cases, pallets and tonnes. Many thanks for your help. Ellie "Pete_UK" wrote: The #VALUE error implies that there is probably something wrong with your data - it might look like numbers or dates but is in fact text. You or your colleague will need to check the data thoroughly. Also, you can't use a full column reference with SP, so if you post the formula you have used here, then we might be able to comment further on it. Pete On Feb 7, 12:12 pm, Ellie wrote: Hi Pete Thanks for the reply. *We have tried this, but unfortunately the sum of the cases, pallets or tonnes covering the respective dates and site selected only brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was the right formula to use. * "Pete_UK" wrote: Why not just return all three values in separate cells, one for Cases, one for Pallets and the third for Tonnes? The SP formula for each of these will be similar - just looking at different columns. Hope this helps. Pete On Feb 7, 11:29 am, Ellie wrote: I hope someone can help my colleague with this one. *He has a spreadsheet in Excel 2003. The main data sheet consists of a series of columns, example as follows:- Col A * * Col B * * *Col C * * *Col D * * *Col E * * * Date * * *Site * * * *Cases * * Plts * * * *Tonnes 1/1/08 * 060 * * * * *25 * * * * *5 * * * * * *2000 1/1/08 * 061 * * * * *50 * * * * 10 * * * * * 3900 etc for approx 20 further sites 2/1/08 * 060 * * * * *32 * * * * *8 * * * * * *3000 2/1/08 * 061 * * * * *45 * * * * *9 * * * * * *3505 and same as for 1/1/08 This continues on one spreadsheet for everydate of the year, plus each of the sites. On a separate sheet within the spreadsheet my colleague is now being required to create a summarised sheet based on a selection of specific dates, i.e. the end-user may select the start date as being 1/1/08 and the end date as being any other date of the week, month or year so could be anywhere between 1 and 350+ days. *The 2nd part of this is whether the figures to be seen for a respective site between dates is in either cases, pallets (plts) or tonnes. We have tried using SUMPRODUCT to calculate the start, end dates and site number, but fail when asking to select whether wanting cases, tonnes or pallets (plts). I would be most grateful if anyone could advise whether there might be a more suitable formula to help assist my colleague with this. If you need any further information, please don't hesitate to contact me.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct mod row | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct | Excel Worksheet Functions |