summing up values with in date intervals

[rushdhih]

I have a Project Advance woksheet. The purspose of the workshhet is to track
all advances that are given to the cash custodians in the field. All advances
are assigned an advance number and any settlement received against the
advance must refer to the advance number.

Every month, advances are analysed according to the days outstanding i.e
0-30 days, 31-60 and 61-90 and 90 and over. Advances are not fully settled
all the time and there can be some left over funds from previous advances.

I need a formula that will achieve this task.

1) The formula should calculate the no of days outstanding by comparing the
date that advance was first issued with today() function.

2) It should sum total amount outstanding against a particular advance
number and show the no. of days outstanding in the corresponding column.


Thank you in advance for your help
Rushdhi

 

[Luke M]

Assuming dates are in column A, values in column C:

Over 90 days:
=SUMPRODUCT(--(TODAY()-A2:A30>90),,B2:B30)
61-90 days:
=SUMPRODUCT(--(TODAY()-A2:A30<91),--(TODAY()-A2:A30>60),B2:B30)
31-60 days:
=SUMPRODUCT(--(TODAY()-A2:A30<61),--(TODAY()-A2:A30>30),B2:B30)
0-30 days:
=SUMPRODUCT(--(TODAY()-A2:A30<31),B2:B30)


Note the structure of the formula, how it uses criteria(s) and values to get
the desired results. If you want counts, remove the array that pertains to
values (B2:B30). Feel free to adjust range sizes/criteria as desired. The
only caution is that you can't callout entire columns (A:A) in SUMPRODUCT,
unless using XL 2007.

 

[Luke M]

Correction: Assumption is that values are in column B