|
Sumproduct count between dates for specific day
Hello
I have the following excel database Area Start End Orange 15/05/08 18/05/08 Orange 18/05/08 27/05/08 Blue 01/04/08 25/04/08 Blue 02/05/08 17/05/08 Yellow 28/04/08 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
Sumproduct count between dates for specific day
Assuming column A is Area, B is Start and C is End, and that you have
100 rows in Sheet1, and further assume that you have dates in column A of Sheet2, then try this in B1 of Sheet2: =SUMPRODUCT(Sheet1!A$1:A$100="Orange")*(Sheet1!B$1 :B$100<=A1)*(Sheet1!C $1:C$100=A1)) Adjust the ranges to suit, and then copy down. Hope this helps. Pete On May 17, 2:39*pm, Hud67 wrote: Hello I have the following excel database Area * * * * * Start * * * * * End Orange * * * *15/05/08 * * 18/05/08 Orange * * * *18/05/08 * * 27/05/08 Blue * * * * * * 01/04/08 * * 25/04/08 Blue * * * * * * 02/05/08 * * 17/05/08 Yellow * * * * *28/04/08 * * 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 * * * How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
Sumproduct count between dates for specific day
This formula counts all but it depends on how you want to treat 18 May. I
used US dates. =SUMPRODUCT(($A$2:$A$22=$D$1)*($B$2:$B$22=$D$2+ROW (A1))+($C$2:$D$22=$D$2+ROW(A1))) area start end orange orange 05/15/2008 05/18/2008 05/14/2008 05/14/2008 orange 05/18/2008 05/27/2008 2 05/15/2008 blue 04/01/2008 04/25/2008 0 05/16/2008 blue 05/02/2008 05/17/2008 2 05/17/2008 yellow 04/28/2008 05/17/2008 3 05/18/2008 0 05/19/2008 0 05/20/2008 0 05/21/2008 0 05/22/2008 0 05/23/2008 0 05/24/2008 0 05/25/2008 0 05/26/2008 1 05/27/2008 0 05/28/2008 -- Don Guillett Microsoft MVP Excel SalesAid Software "Hud67" wrote in message ... Hello I have the following excel database Area Start End Orange 15/05/08 18/05/08 Orange 18/05/08 27/05/08 Blue 01/04/08 25/04/08 Blue 02/05/08 17/05/08 Yellow 28/04/08 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
Sumproduct count between dates for specific day
Dear Pete
Thanks for that, but it's not working. Here an example Sheet1 (thats the database): Area Start End Orange 04/01/2008 12/01/2008 Orange 12/01/2008 15/01/2008 Blue 07/01/2008 10/01/2008 Yellow 05/01/2008 09/01/2008 Sheet2 (calculations) with the following example formula for Cell B2 =SUMPRODUCT(Sheet1!$A$2:$A$100="Yellow")*(Sheet1!$ B$2:$B$100<=$A2)*(Sheet1!$C$2:$C$100=$A2) As you see all the results are 0 Date Yellow Blue Orange 01/01/2008 0 0 0 02/01/2008 0 0 0 03/01/2008 0 0 0 04/01/2008 0 0 0 05/01/2008 0 0 0 06/01/2008 0 0 0 07/01/2008 0 0 0 08/01/2008 0 0 0 09/01/2008 0 0 0 10/01/2008 0 0 0 11/01/2008 0 0 0 12/01/2008 0 0 0 13/01/2008 0 0 0 14/01/2008 0 0 0 15/01/2008 0 0 0 And here is a manually result list (as it should be with formulas): Date Yellow Blue Orange 01/01/2008 02/01/2008 03/01/2008 04/01/2008 1 05/01/2008 1 1 06/01/2008 1 1 07/01/2008 1 1 1 08/01/2008 1 1 1 09/01/2008 1 1 1 10/01/2008 1 1 11/01/2008 1 12/01/2008 2 13/01/2008 1 14/01/2008 1 15/01/2008 1 16/01/2008 Thanks again for help. Daniel "Pete_UK" wrote: Assuming column A is Area, B is Start and C is End, and that you have 100 rows in Sheet1, and further assume that you have dates in column A of Sheet2, then try this in B1 of Sheet2: =SUMPRODUCT(Sheet1!A$1:A$100="Orange")*(Sheet1!B$1 :B$100<=A1)*(Sheet1!C $1:C$100=A1)) Adjust the ranges to suit, and then copy down. Hope this helps. Pete On May 17, 2:39 pm, Hud67 wrote: Hello I have the following excel database Area Start End Orange 15/05/08 18/05/08 Orange 18/05/08 27/05/08 Blue 01/04/08 25/04/08 Blue 02/05/08 17/05/08 Yellow 28/04/08 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
Sumproduct count between dates for specific day
Dear Don
Thank you. Sorry but I don't get it. Could you please check my other reply with an example. Cheers Daniel "Don Guillett" wrote: This formula counts all but it depends on how you want to treat 18 May. I used US dates. =SUMPRODUCT(($A$2:$A$22=$D$1)*($B$2:$B$22=$D$2+ROW (A1))+($C$2:$D$22=$D$2+ROW(A1))) area start end orange orange 05/15/2008 05/18/2008 05/14/2008 05/14/2008 orange 05/18/2008 05/27/2008 2 05/15/2008 blue 04/01/2008 04/25/2008 0 05/16/2008 blue 05/02/2008 05/17/2008 2 05/17/2008 yellow 04/28/2008 05/17/2008 3 05/18/2008 0 05/19/2008 0 05/20/2008 0 05/21/2008 0 05/22/2008 0 05/23/2008 0 05/24/2008 0 05/25/2008 0 05/26/2008 1 05/27/2008 0 05/28/2008 -- Don Guillett Microsoft MVP Excel SalesAid Software "Hud67" wrote in message ... Hello I have the following excel database Area Start End Orange 15/05/08 18/05/08 Orange 18/05/08 27/05/08 Blue 01/04/08 25/04/08 Blue 02/05/08 17/05/08 Yellow 28/04/08 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
Sumproduct count between dates for specific day
Pete's suggestion should work for you, but you are missing a couple of
parentheses. Try this amended version: =SUMPRODUCT((Sheet1!$A$2:$A$100="Yellow")*(Sheet1! $B$2:$B$100<=$A2)*(Sheet1!$C$2:$C$100=$A2)) "Hud67" wrote: Dear Don Thank you. Sorry but I don't get it. Could you please check my other reply with an example. Cheers Daniel "Don Guillett" wrote: This formula counts all but it depends on how you want to treat 18 May. I used US dates. =SUMPRODUCT(($A$2:$A$22=$D$1)*($B$2:$B$22=$D$2+ROW (A1))+($C$2:$D$22=$D$2+ROW(A1))) area start end orange orange 05/15/2008 05/18/2008 05/14/2008 05/14/2008 orange 05/18/2008 05/27/2008 2 05/15/2008 blue 04/01/2008 04/25/2008 0 05/16/2008 blue 05/02/2008 05/17/2008 2 05/17/2008 yellow 04/28/2008 05/17/2008 3 05/18/2008 0 05/19/2008 0 05/20/2008 0 05/21/2008 0 05/22/2008 0 05/23/2008 0 05/24/2008 0 05/25/2008 0 05/26/2008 1 05/27/2008 0 05/28/2008 -- Don Guillett Microsoft MVP Excel SalesAid Software "Hud67" wrote in message ... Hello I have the following excel database Area Start End Orange 15/05/08 18/05/08 Orange 18/05/08 27/05/08 Blue 01/04/08 25/04/08 Blue 02/05/08 17/05/08 Yellow 28/04/08 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
Sumproduct count between dates for specific day
Hi
It works perfect! You are all superstars. Thanks a lot. By the way, i have another small question. I have also amounts in another row and would like to sum them up for the month (related to the start date) an (e.g. yellow etc.). What would be the correct sumproduct formula? Thanks again "daddylonglegs" wrote: Pete's suggestion should work for you, but you are missing a couple of parentheses. Try this amended version: =SUMPRODUCT((Sheet1!$A$2:$A$100="Yellow")*(Sheet1! $B$2:$B$100<=$A2)*(Sheet1!$C$2:$C$100=$A2)) "Hud67" wrote: Dear Don Thank you. Sorry but I don't get it. Could you please check my other reply with an example. Cheers Daniel "Don Guillett" wrote: This formula counts all but it depends on how you want to treat 18 May. I used US dates. =SUMPRODUCT(($A$2:$A$22=$D$1)*($B$2:$B$22=$D$2+ROW (A1))+($C$2:$D$22=$D$2+ROW(A1))) area start end orange orange 05/15/2008 05/18/2008 05/14/2008 05/14/2008 orange 05/18/2008 05/27/2008 2 05/15/2008 blue 04/01/2008 04/25/2008 0 05/16/2008 blue 05/02/2008 05/17/2008 2 05/17/2008 yellow 04/28/2008 05/17/2008 3 05/18/2008 0 05/19/2008 0 05/20/2008 0 05/21/2008 0 05/22/2008 0 05/23/2008 0 05/24/2008 0 05/25/2008 0 05/26/2008 1 05/27/2008 0 05/28/2008 -- Don Guillett Microsoft MVP Excel SalesAid Software "Hud67" wrote in message ... Hello I have the following excel database Area Start End Orange 15/05/08 18/05/08 Orange 18/05/08 27/05/08 Blue 01/04/08 25/04/08 Blue 02/05/08 17/05/08 Yellow 28/04/08 17/05/08 etc. On a separat worksheet i would like to calculate the following for every day of the year. 01/01/08 How many Orange where active on that day 01/02/08 etc. I tried it with summproduct but can't figure out the correct formula to calculate between the start and end date. Thank you very much for your help. |
| All times are GMT +1. The time now is 07:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com