Formula sum monthly project management fees by Fiscal Year

ama1

New Member
Joined
Dec 6, 2022
Messages
6
Office Version
  1. 365
  2. 2021
1670385226805.png

Really stuck on this one. I have projects that are running between 2 dates (Project Approval Date and the Estimated Completion date). I need to summarize the monthly project management fee (column G) by Fiscal Year.

Overall, I am trying to create a formula to calculate the amounts in column L through to Q, to summarize the monthly journal amounts by fiscal year.

I am really stuck on this one and I am hoping that someone out there can assist.

Sincere thanks in advance,

A
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Upvote 0
Upvote 0
Solution
You can consider
T202102a.xlsm
BCDEFGHIJKL
2StartEndAmountDaysDaily Amt30-Jun-22202320242025
301-May-2501-Jun-25$71,890.80322,246.590.000.000.0071,890.80
430-Jun-2401-Feb-25$30,865.80217142.240.000.00142.2430,723.56
530-Jul-2201-Feb-23$30,967.90187165.600.0030,967.900.000.00
601-Jul-2230-Jun-25$82,200.00109675.000.0027,375.0027,450.0027,375.00
7
6cc
Cell Formulas
RangeFormula
G3:G6G3=C3-B3+1
H3:H6H3=F3/(C3-B3+1)
I3:L6I3=MAX(0, MIN(I$2, $C3) - MAX(EDATE(I$2,-12), $B3-1))*$H3
Thanks for the quick response. I may have to resort to using a daily amount. However, the preferred option would be to keep it as monthly to align with the monthly journal amount.
 
Upvote 0
Does 1 day mean a month's allocation?
Does any number of days in a month mean a month's allocation and all months are allocated the same amount?
 
Upvote 0
N.B. Your image show allocation to years.
You could prepare a similar sheet but by month and use that for your journal entry.
You could summarize the monthly amounts for the annual total.
 
Upvote 0
You can consider

T202102a.xlsm
ABCDEFGHIJKL
1
2StartEndAmountDaysDaily Amt30-Jun-22202320242025
301-May-2501-Jun-25$71,890.80322,246.590.000.000.0071,890.80
430-Jun-2401-Feb-25$30,865.80217142.240.000.00142.2430,723.56
530-Jul-2201-Feb-23$30,964.90187165.590.0030,964.900.000.00
601-Jul-2230-Jun-25$82,200.00109675.000.0027,375.0027,450.0027,375.00
7
6cc
Cell Formulas
RangeFormula
G3:G6G3=C3-B3+1
H3:H6H3=F3/(C3-B3+1)
I3:L6I3=MAX(0, MIN(I$2, $C3) - MAX(EDATE(I$2,-12), $B3-1))*$H3
THanks heaps. This solved my problem. I ended up changing it yo
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top