Sum value between two dates and copy to new cell

[dave]

Hi Guys,

Thanks for all the previous helps and guidance on excel sheet and im
looking forward for new assistants. i've various dates in column A and
values in column B. These values should sum and copy to column E
according to date between column C & D, example as below,

A B C
D E

Total
04/01/2010 12 01/01/2010 28/02/2010
02/04/2010 133 01/03/2010 30/04/2010
05/06/2010 154 01/05/2010 30/06/2010
03/04/2010 112 01/07/2010 31/08/2010
05/05/2010 65 01/09/2010 31/10/2010
06/07/2010 34 01/11/2010 31/12/2010
02/01/2010 26
02/03/2010 25
14/02/2010 85
17/08/2010 99

Thanks in advance.

dave

 

[joel]

Use sumproduct


=SUMPRODUCT(--(A1>=C$1:C$5),--(A1<=D$1$5),B$1:B$5)

 

[Bob Phillips]

=SUMIF(C:C,">="&A2,B:B)SUMIF(D,">"&A2,B:B)

 

[dave]

=SUMIF(C:C,">="&A2,B:B)SUMIF(D,">"&A2,B:B)

--

HTH

Bob











- Show quoted text -

hI Guys,

Im getting an error value. I guess i've provided wrong explain and its
confusing.
Thanks for all the previous helps and guidance on excel sheet and im
looking forward for new assistants. i've various dates in column A
and
values in column B. These values should sum and copy to column E
according to the date. C1 is 01/01/10 and D1 is 28/02/10. so all the
value between these dates should be sum and display in column E.

A B C
D E

04/01/2010 12 01/01/2010 28/02/2010
02/04/2010 133 01/03/2010 30/04/2010
05/06/2010 154 01/05/2010 30/06/2010
03/04/2010 112 01/07/2010 31/08/2010
05/05/2010 65 01/09/2010 31/10/2010
06/07/2010 34 01/11/2010 31/12/2010
02/01/2010 26
02/03/2010 25
14/02/2010 85
17/08/2010 99

Thank you

Dave

 

[Roger Govier]

Hi Dave

Bob inadvertently omitted the - sign between his 2 Sumifs
=SUMIF(C:C,">="&A2,B:B)-SUMIF(D,">"&A2,B:B)

equally, Joel's formula should work fine. You might need to extend the
ranges.

If you are still getting error's, then there must be something wrong
with your data.
Are they true Excel dates, or Text dates?

 

[dave]

Hi Dave

Bob inadvertently omitted the - sign between his 2 Sumifs
=SUMIF(C:C,">="&A2,B:B)-SUMIF(D,">"&A2,B:B)

equally, Joel's formula should work fine. You might need to extend the
ranges.

If you are still getting error's, then there must be something wrong
with your data.
Are they true Excel dates, or Text dates?

--
Regards
Roger Govier










- Show quoted text -

Hi Roger,

Below is what is the output i received.
A B
C D E
04/01/2010 12 01/01/2010 28/02/2010 -12
02/04/2010 133 01/03/2010 30/04/2010 -133
05/06/2010 154 01/05/2010 30/06/2010 -154
03/04/2010 112 01/07/2010 31/08/2010 -133
05/05/2010 65 01/09/2010 31/10/2010 -154
06/07/2010 34 01/11/2010 31/12/2010 -112
02/01/2010 26
02/03/2010 25
14/02/2010 85
17/08/2010 99

Im getting -12. Its should be 12 + 26 +85 = 123. all these values are
between 01/01/10 and 28/02/10. any solution?

cheers

dave

 

[Roger Govier]

Hi Dave

I'm sorry, I just looked at the logic of the formulae, without looking
at your data

Change Bob's formula to
=SUMIF(A:A,">"&C2,B:B)-SUMIF(A:A,">="&D2,B:B)
or change Joel's formula to
=SUMPRODUCT(--($A$2:$A$11>=$C2),--($A$2:$A$11<=$D2),B$2:B$11)

 

[dave]

Hi Dave

I'm sorry, I just looked at the logic of the formulae, without looking
at your data

Change Bob's formula to
=SUMIF(A:A,">"&C2,B:B)-SUMIF(A:A,">="&D2,B:B)
or change Joel's formula to
=SUMPRODUCT(--($A$2:$A$11>=$C2),--($A$2:$A$11<=$D2),B$2:B$11)

--
Regards
Roger Govier







- Show quoted text -

Hi Roger,

Its working absolutely perfect. thanks alot mate.

Thank you

dave