Summing rows based on other column values

[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

 

[T. Valko]

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),--(D29>=H2),--(D29<=I2),C2:C9)

 

[Shane Devenshire]

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"),--(D18>=DATE(2009,1,1)),--(D18<=DATE(2009,7,1)),C1:C8)

 

[Shane Devenshire]

Hi,

You can also use the slightly shorter

=SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D18>=--"1/1/09"),--(D18<=--"7/1/09"),C1:C8)

Or you can reference two cells with the dates (A1 & A2)

=SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D18>=A1),--(D18<=A2),C1:C8)

 

[jonny]

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"),--(D18>=DATE(2009,1,1)),--(D18<=DATE(2009,7,1)),C1:C8)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Brill thanks guys that's a massive help.. thanks so much

 

[jonny]

Hi,

You can also use the slightly shorter

=SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D18>=--"1/1/09"),--(D18<=--"7/1/09"),C1:C8)

Or you can reference two cells with the dates (A1 & A2)

=SUMPRODUCT(--(LEFT(B1:B8,6)="ght991"),--(D18>=A1),--(D18<=A2),C1:C8)
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

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

 

[Shane Devenshire]

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.

 

[T. Valko]

Try this:

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