Summing rows based on other column values

  • Thread starter Thread starter jonny
  • Start date Start date
J

jonny

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
 
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)
 
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)
 
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)
 
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
Click to expand...

Brill thanks guys that's a massive help.. thanks so much
 
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
Click to expand...

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
 
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.
 
Try this:

=SUMPRODUCT(--(A2:A9=F2),--(LEFT(B2:B9,6)=G2),--(INT(D2:D9)>=H2),--(INT(D2:D9)<=I2),C2:C9)
 
Back
Top