Forecasting number/amt of payments

[GQuinn]

Hi all,

My company purchases certain items from vendors. The price for these purchases are fixed by contract. However, they occur on different dates. I am looking to forecast how much we will pay per month.

1) I would like Excel to calculate the number of payments made every month of each year in the dataset for every month from September 2010 to September 2050.
a) For example, to know how payments made September 2010, how many payments made Oct. 2010….all the way to how many payments made August 2050 and how many payments made September 2050.
b) I will not know the range of the data; it will change on every spreadsheet I receive. My supervisor does not want to manually calculate the range of the data; can excel calculate the entire range and then feed it into this formula for me?


2) I would like Excel to then sum how much would be paid each month.
a) If a certain vendor is paid for a month, we will pay the price equal to the “price” variable to that vendor.
b) I would like to sum the amount paid to all the vendors we purchase from every month.

Attached is an example worksheet showing the type of data I have. I showed the numbers I would like to be automatically summed. Let me know if this is unclear.

I am sure this is probably very easy; I am not very experienced in excel

Thank you very much.

GQuinn

[MickG]

Hi, Try this :- Results start "G2"

Please Login or Register  to view this content.

Regards Mick

[GQuinn]

Mick,

you are absolutely brilliant. that worked perfectly!

One question (due to my poor explanation): for the amt paid each month would it be possible for it to be completely summed for each month so that

for a month, the results would not be 2000-800 but 2800?

let me know if that is not clear

thank you so much!

[MickG]

Hi, Glad it working.
For the Change of "Amt" range
Change code line:-

Please Login or Register  to view this content.

to Code line :-

Please Login or Register  to view this content.

Regards Mick