Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf and ranges | Excel Worksheet Functions | |||
SUMIF ranges | Excel Worksheet Functions | |||
Sumif Using Date ranges | Excel Discussion (Misc queries) | |||
SUMIF using two ranges | Excel Discussion (Misc queries) | |||
Calculate a SUMIF if criteria is between 2 date ranges | Excel Worksheet Functions |