|
Summing rows based on other column values
Hi All,
I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon |
Summing rows based on other column values
Use cells to hold the criteria:
F2 = Bob G2 = ght991 H2 = lower date boundary I2 = upper date boundary =SUMPRODUCT(--(A2:A9=F2),--(LEFT(B2:B9,6)=G2),--(D2:D9=H2),--(D2:D9<=I2),C2:C9) -- Biff Microsoft Excel MVP "jonny" wrote in message ... Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon |
Summing rows based on other column values
Hi,
Your original formula would have been better done with: =SUMIF(B1:B8,"GHT991*",C1:C8) For the current question: =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=DATE(2009,1,1)),--(D1:D8<=DATE(2009,7,1)),C1:C8) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon |
Summing rows based on other column values
Hi,
You can also use the slightly shorter =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=--"1/1/09"),--(D1:D8<=--"7/1/09"),C1:C8) Or you can reference two cells with the dates (A1 & A2) =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=A1),--(D1:D8<=A2),C1:C8) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon |
Summing rows based on other column values
On 28 Jan, 22:02, Shane Devenshire
wrote: Hi, Your original formula would have been better done with: =SUMIF(B1:B8,"GHT991*",C1:C8) For the current question: =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=DATE(2009,1,1)),--(D1:D8<=DATE(2009,7,1)),C1:C8) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, * 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon Brill thanks guys that's a massive help.. thanks so much |
Summing rows based on other column values
On 28 Jan, 22:05, Shane Devenshire
wrote: Hi, You can also use the slightly shorter =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=--"1/1/09"),--(D1:D8<=--"7/1/09"),C1:C8) Or you can reference two cells with the dates (A1 & A2) =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=A1),--(D1:D8<=A2),C1:C8) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, * 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon Shane, Quick question.. I've just realised that my dates are formatted as "01/12/2008 15:44" and this seems to be stopping the formula from working, is there a way around this? By the way I can't see a "yes" button on your posts, but you are helping! Thanks, Jon |
Summing rows based on other column values
Hi,
Formatting will make no difference. The real question is are they really dates? Select one of the date and choose Format, Cells, Number tab - is the Date category selected? If not they are not dates. Let us know what you find. -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: On 28 Jan, 22:05, Shane Devenshire wrote: Hi, You can also use the slightly shorter =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=--"1/1/09"),--(D1:D8<=--"7/1/09"),C1:C8) Or you can reference two cells with the dates (A1 & A2) =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=A1),--(D1:D8<=A2),C1:C8) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon Shane, Quick question.. I've just realised that my dates are formatted as "01/12/2008 15:44" and this seems to be stopping the formula from working, is there a way around this? By the way I can't see a "yes" button on your posts, but you are helping! Thanks, Jon |
Summing rows based on other column values
Try this:
=SUMPRODUCT(--(A2:A9=F2),--(LEFT(B2:B9,6)=G2),--(INT(D2:D9)=H2),--(INT(D2:D9)<=I2),C2:C9) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Formatting will make no difference. The real question is are they really dates? Select one of the date and choose Format, Cells, Number tab - is the Date category selected? If not they are not dates. Let us know what you find. -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: On 28 Jan, 22:05, Shane Devenshire wrote: Hi, You can also use the slightly shorter =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=--"1/1/09"),--(D1:D8<=--"7/1/09"),C1:C8) Or you can reference two cells with the dates (A1 & A2) =SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D1:D8=A1),--(D1:D8<=A2),C1:C8) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jonny" wrote: Hi All, I have a table something like the below: Column A, B, C, D bob, ght991xyz, 10, 10/01/09 jan, ght887fht, 100, 03/01/09 Bob, ght991xyz, 50, 09/01/09 jan, ght887fht, 7, 12/02/09 bob, ght991xyz, 10, 04/02/09 jan, ght887fht, 200, 02/01/09 rupert, ght991xyz, 50, 07/01/09 jan, ght887fht, 7, 09/01/09 I'm currently using the following formula: =SUMPRODUCT((LEFT($B$1:$B$9,6)="ght991")*($C$1:$C$ 9)) the above checks the first 6 characters of Column B are equal to "GHT991" and then sums the value in column c for the rows where this is the case... What I now need to do is find a way to sum column C only when Column A is equal to "bob" and column B begins with "GHT991" and where column D is between a specified date range say 01/01/09 and 07/01/09 (I'm UK based by the way so it's ddmmyy). Any help much appreciated.. I'm stumped! Thanks, Jon Shane, Quick question.. I've just realised that my dates are formatted as "01/12/2008 15:44" and this seems to be stopping the formula from working, is there a way around this? By the way I can't see a "yes" button on your posts, but you are helping! Thanks, Jon |
| All times are GMT +1. The time now is 12:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com