SUMIF with date ranges
 

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

SUMIF with date ranges
 

=SUMPRODUCT(--(A1:A100=C1-60),--(A1:A100<C1-30),D1:D100)


adjust to fit

you might also want this


=SUMPRODUCT(--(A1:A100=C1-60),--(A1:A100<=C1-30),D1:D100)


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

--


Regards,


Peo Sjoblom

"Vivian0102" wrote in message
...
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

SUMIF with date ranges
 

Hi,

In the range B99:D99 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 D1:D4 to represent your ranges. You
can move the range A99:D99 to any location you wish, but A99 should be empty
or 0.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Vivian0102" wrote:

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