SUMIF with date ranges

[Vivian0102]

Hi,

I need help with a formula to add the sum of values in column D if a date in
column A is between 31-60 before C1 (A100), or between 61-90 before C1
(B100), or between 91-120 before C1 (C100) and so fort.

Example:

C1= 10/31/08

A1=8/31/08 D1= $1,200
A2=9/30/08 D2= $1,200
A3=9/30/08 D3= $1,200
A4=10/31/08 D4= $1,200

A100= Total of 30-60 B100= Total of 60-90 C100= Total of 90-120

 

[Peo Sjoblom]

=SUMPRODUCT(--(A1:A100>=C1-60),--(A1:A100<C1-30),D1100)


adjust to fit

you might also want this


=SUMPRODUCT(--(A1:A100>=C1-60),--(A1:A100<=C1-30),D1100)


that would be the 30-60 days, the rest you should be able to figure out
yourself

--


Regards,


Peo Sjoblom

 

[ShaneDevenshire]

Hi,

In the range B9999 enter the numbers 60,90,120 respectively.
In A100 enter
=SUMPRODUCT(--(($C1-$A1:$A4)<=B99),--(($C1-$A1:$A4)>A99),$D1:$D4)
Copy this formula to the right to column C.

You will need to adjust the A1:A4 and D14 to represent your ranges. You
can move the range A9999 to any location you wish, but A99 should be empty
or 0.

If this helps, please click the Yes button.