# Need formula to calculate monthly costs based on date range

1. ## Need formula to calculate monthly costs based on date range

Monthly view of costs.xlsx
Attached is a file that contains 2 worksheets: Data and Monthly

The Data sheet contains data that shows the total amount for a data range per Batch and Type.
The Monthly sheet lists each unique entry of Batch and Type. I need a formula to enter into cells (C2-CH10) that will calculate those total costs on the Data sheet into the monthly amount and discplay that amount into those cells. I've hard entered the amounts the formula should return.

Thanks for any assistance.  Register To Reply

2. ## Re: Need formula to calculate monthly costs based on date range

See attached based on Dates in column C: If I use column D I get zeros .... which I don't understand.  Register To Reply

3. ## Re: Need formula to calculate monthly costs based on date range

...... solved ... bad day at the office ....!!!

=SUMIFS(Data!\$F\$2:\$F\$66,Data!\$A\$2:\$A\$66,Monthly!\$A2,Data!\$B\$2:\$B\$66,Monthly!\$B2,Data!\$D\$2:\$D\$66,">=" &Monthly!C\$1,Data!\$D\$2:\$D\$66,"<=" &EOMONTH(Monthly!C\$1,0))  Register To Reply

4. ## Re: Need formula to calculate monthly costs based on date range

Hi John,

Thank you for your attempt at this. Unfortunately when I copy that formula into cell C2 on the Monthly worksheet and drag down and across, I don't get quite the results I'm expecting. For example, Batch 2 HW: the formula calculates \$7,367.22 in Dec-10 and nothing else until Dec-11. But it should have calculated \$4,148.55 for Dec-10 all the way through Nov-14.  Register To Reply

5. ## Re: Need formula to calculate monthly costs based on date range

I confess to not understanding the calculation required! how do you get \$4,148.55 for Dec-10 to Nov-14? I am obviously "brain dead" today (and yesterday!).  Register To Reply

6. ## Re: Need formula to calculate monthly costs based on date range

Monthly view of costs.xlsx
In this file you will see months going across with costs in them on the Data worksheet. This is only there to show how each monthly costs is calculated. These fields will not be there in the final file.

The Monthly worksheet has the corrected dollar amounts as it apears in my original file, I had some calculations in the incorrect months. I apologize for the confusion on this.

Thanks.  Register To Reply

7. ## Re: Need formula to calculate monthly costs based on date range

Still struggling I'm afraid. I need to consolidate in "helper" table (Batch, Type, Start/End dates) and then work out how to allocate.

I'll look again tomorrow as brained has died again!  Register To Reply

8. ## Re: Need formula to calculate monthly costs based on date range

The months going across in the Data worksheet show how the monthly amounts are calculated - it's the Total Cost spread over the number of months as shown in the Duration column and beginning in the month year in the Start column.  Register To Reply

9. ## Re: Need formula to calculate monthly costs based on date range

Please take a look at the attached.

I have added "helper" columns in "Data" to try to accumulate data over the required periods: the start and end columns designate the months (columns) for the data to be inserted (1= Jan 2010). I also added a column in "Month" for a search key used by the macro.

In the absence of any other solution I used a simple macro to assign the values to the months.  Register To Reply

10. ## Re: Need formula to calculate monthly costs based on date range

Can you tell me where I can find the formula in the Monthly worksheet that will show the monthly totals per Batch and Type?
Thanks.  Register To Reply

11. ## Re: Need formula to calculate monthly costs based on date range

There is no formula in the Monthly work sheet.

The calculation of monthly totals is done in the "helper" columns in "DATA". There is SUMIFS calculation in Column J which sums by Batch/Type/Start Date/End Date.

There are repetitive answers which the macro ignores when it summates for a particular month.

I cloud not think of another way of doing it.  Register To Reply

12. ## Re: Need formula to calculate monthly costs based on date range

John, thank you for all your work. Unfortunately I could not use the file you sent as this was just a sample of a much larger file. But you gave me an idea on how write the formula I needed. This issue is now solved thanks to your ideas.  Register To Reply

13. ## Re: Need formula to calculate monthly costs based on date range

For my own education I would be interested in your solution as I often "can't see the wood for the trees"!

Thanks for feedback: sorry I could not provide a better solution.  Register To Reply

14. ## Re: Need formula to calculate monthly costs based on date range

My solution: in the Monthly worksheet in cell C2 I put the following formula and dragged across and down:
=SUMPRODUCT((Data!\$C\$2:\$C\$66<=C\$1)*(Data!\$D\$2:\$D\$66>=C\$1)*(Data!\$A\$2:\$A\$66=\$A2)*(Data!\$B\$2:\$B\$66=\$B2),Data!\$G\$2:\$G\$66)  Register To Reply

15. ## Re: Need formula to calculate monthly costs based on date range

I forgot to mention that in the Data worksheet I added Monthly Totals in column G of the Data worksheet.  Register To Reply

16. ## Re: Need formula to calculate monthly costs based on date range

Thank you.

I haven't looked at the spreadsheet and applied your formula but it was the "missing" monthly totals which "prevented" me from using SUMPRODUCT in my earlier attempts.

So I'll claim a little merit for the solution!  Register To Reply

17. ## Re: Need formula to calculate monthly costs based on date range Yes, it was the Monthly Totals column that I needed and hadn't come up with without your assistance.  Register To Reply