Aging Formula Help

[klmiura]

I have a worksheet with column AD currently showing the number of days based
upon todays date they are past due. I have the following criteria that I
want the formula to return in column AC if the number in columns D is between
those numbers.

Days:
0-30
31-60
61-120
121-180
181-240
241-300
365+

 

[Bob Phillips]

=COUNTIF(AD:AD,"<=30")

=COUNTIF(AD:AD,"<=60")-COUNTIF(AD:AD,"<=30")

etc.

 

[Peo Sjoblom]

I assume you mean column AD and not D?


=VLOOKUP(AD1,{0,"0-30";31,"31-60";61,"61-120";121,"121-180";181,"181-300";241,"241-300";301,"365+"},2)


replace AD1 with the cell you want to use


copy down




--


Regards,


Peo Sjoblom

 

[Dave]

Hi,
Modify your table to look like this:
AA_____AB
0______0-30
31_____31-60
61_____61-120
121____121-180
181____181-240
241____241-300
301____301-365
365____365+

If this table is in AA1:AA8, then:
=VLOOKUP(D2,$AA$1:$AA$8,2)

Regards - Dave.

 

[ryguy7272]

In A2:A8 put the following:
0
31
61
121
181
241
365

In D1, put this:
=IF(A2="","",SUM($A$2:A2)-A2+1&IF(A2=1,""," - "&SUM($A$2:A2)))

Fill down.

Regards,
Ryan---