Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing up values with in date intervals
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing up values with in date intervals
Assuming dates are in column A, values in column C:
Over 90 days: =SUMPRODUCT(--(TODAY()-A2:A3090),,B2:B30) 61-90 days: =SUMPRODUCT(--(TODAY()-A2:A30<91),--(TODAY()-A2:A3060),B2:B30) 31-60 days: =SUMPRODUCT(--(TODAY()-A2:A30<61),--(TODAY()-A2:A3030),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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "rushdhih" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summing up values with in date intervals
Correction: Assumption is that values are in column B
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Assuming dates are in column A, values in column C: Over 90 days: =SUMPRODUCT(--(TODAY()-A2:A3090),,B2:B30) 61-90 days: =SUMPRODUCT(--(TODAY()-A2:A30<91),--(TODAY()-A2:A3060),B2:B30) 31-60 days: =SUMPRODUCT(--(TODAY()-A2:A30<61),--(TODAY()-A2:A3030),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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "rushdhih" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing values in a column if the date in another is of a certain month | Excel Discussion (Misc queries) | |||
Summing values in a column if the date in another is of a certain month | New Users to Excel | |||
Summing values in a column if the date in another is of a certain month | Excel Worksheet Functions | |||
sum up values at intervals | Excel Discussion (Misc queries) | |||
Adding values for prior date intervals | Excel Worksheet Functions |