conditional sum based on date in two columns

[laxmikanth.ponna]

Hi,

I have three columns where column A & B contains date and column C contains the number. I want Column D to populate with sum of column C where date in column A is less than column B.

D1 is 2+3+4 (C2+C3+C4) as A2<B1,A3<B1,A4<B1
D2 is 3+4 (C3+C4) as A3<B1,A4<B1

and so on....

Does any one had idea on how to achieve this?

Col A Col B Col C Col D
1 09-Jan-12 19-Feb-13 2 =c2 + C3 +C4
2 10-Mar-12 19-Nov-12 2 7 =c3+c4
3 24-Aug-12 15-Apr-13 3 9 = c4+c5
4 18-Oct-12 15-Apr-13 4 5 = c5
5 15-Mar-13 15-Apr-13 5 0


Regards,
Kanth

[ChemistB]

This works for your example

=IF(OR(NOT(ISNUMBER(A2)),A2>B1),0,SUM(C2:INDEX(C2:$C$5,MATCH(B1,A2:$A$5))))