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
|