Category SUM by Year - Help Needed. Thanks!

[JimBucklin]

Hi, I have been racking my brain on this one and can’t figure out a solution…

I have a spreadsheet that tracks expenses by category (Staffing, Travel & Entertainment, MKTG, etc.). I want to be able to display the data by “expense category” and by “year” on a pie chart or graph.

As the sheet is now, I enter a description of the expense in column A and then select from a dropdown in column C (period) to identify the expense as monthly or one-time. I then enter the “Date / Begin Date” (column E) of the expense. The monthly expenses are calculated on a going forward basis for each year through 2018. In other words, if I enter Office Rent of $2,000 as a monthly expense that starts in Jan 2014, each month going forward has the $2,000 expense in the total. One-time expenses are only added to the month they are incurred. The expense totals are then added to a separate tab as “recurring” or “non-recurring” by month and compared to sales forecast numbers to measure cash flow.

I added a column (B) to the sheet for each “expense category” which is selected from a dropdown list.
I need help with the formula to separate the expense by category and calculate the total by year based on the dates from E. I had help creating this sheet and I’m over my head on this new feature… Any help is greatly appreciated!!

Attached is an example sheet with a mock-up of how the inputs are now. Unfortunately, I can’t post the actual sheet due to the info already in there…

Many thanks!

[TMS]

Try: B3:

Please Login or Register  to view this content.

copied across and down


Regards, TMS

[JimBucklin]

Hi Trevor, thanks for your help! The formula gave the total for the single year but does not carry over the following years and the monthly expenses are recurring thru 2018... Forgive me but I'm a "power user" at best and provided a poor example of the sheet... I uploaded a new file that will show you the data better... The person who created the sheet doesn't work with me any longer so I really appreciate the help!

Cheers,

Jim

[Saarang84]

Hi Jim,

TMS's formula works fine.. You just need to adjust the worksheet ranges used. Refer enclosed workbook.

Your formulas were referring to incorrect ranges and also you missed including one or two Expense categories.

[JimBucklin]

Yeah, there were several hidden rows that I didn't have on the first file... Since the "monthly" expenses are ongoing, they should appear each year. Any ideas?

[JimBucklin]

Hi, the new sheet also doesn't seem to allow for new expenses. I tried adding a few and they didn't appear - both in categories that aren't currently populated with an amount (like sales commissions) and in new dates/years (like staffing-employee for 2017). I think the original sheet allows 2000 rows in the expense tab to accommodate for new additions. Thanks again!

[TMS]

I've made the Expense Categories List a Dynamic Named Range and adjusted the formula to reflect the different data range. See the attachment.

B3:

Please Login or Register  to view this content.

Regards, TMS

[JimBucklin]

Hi Trevor,

The expenses that recur should do so for each subsequent year - so a monthly or quarterly expense in 2014 would also be duplicated in 2015, 2016,2017 & 2018, all starting from the date entered in E (date / begin date). Also, is there a way to allow for additional rows on the expense sheet? Right now it's limited to 73 so I can't add any new expenses - the original sheet has up to 2000 so there can be additional expenses added.

Thank you so much! I wish I had your skill

[TMS]

Suggest you try taking out the date check part where it is Monthly or Quarterly.

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Sorry, missed this question:

Also, is there a way to allow for additional rows on the expense sheet?

Make the raw data table into a "real" Excel Table. Then you can use Structured Table References rather then fixed ranges. A Table will maintain the Data Validation, formatting and formulae on new rows, whereas, a Dynamic Named Range wouldn't necessarily.

Regards, TMS

[JimBucklin]

Thanks again Trevor, like I said; I wish I had your skill. Sadly, I don't know how to do this... If it's a timely process maybe I can find someone to help locally. Many thanks for all your time!

[TMS]

Click somewhere in the data, select Insert | Table ... leave it with me, I'll try and have a look later.

[TMS]

Updated workbook.

Regards, TMS

[JimBucklin]

Wow Trevor! This is awesome! I see one thing that I tried to correct but there's so much to this formula that I'm afraid I can't seem to find the solution... I copied the cells of B (expense categories) beyond row 73 to make room for additional entries. I also copied the cells in the remaining columns so the formulas are in there... As a test; I added a "sales Commission" as a one-time expense and it went into the correct year but when I added it as a monthly or quarterly expense starting in '15, '16, '17,' or 2018 it started the expense in 2014. It seems all the monthly expenses do this. Also, since the "Misc." category is currently a $0 I tested it as well and it doesn't seem to work, as a category - not a big deal considering how great this tool is!

Is it OK to delete the Summary (2) and Expenses (2) sheets?

You are amazing! Thank you so much!!

[TMS]

Play with this version ...

[JimBucklin]

On this one, the Misc. category doesn't work and monthly or quarterly expenses entered in with a date / begin date of 2015, 2016, 2017 or 2018 all start in 2014. Example: If I enter a monthly expense in June 2015, it should calculate 6 months of 2015 and 12 mos. each for 2016, 2017 & 2018. Right now, it calculates the amount multiplied by 12 months and starts back to 2014.

[TMS]

Back in Post #9, I said:

Suggest you try taking out the date check part where it is Monthly or Quarterly.

Which is what I did However, the downside of that decision that it applies monthly and quarterly values across the whole table.

And, with regard to applying say 6 months worth of monthly payments, I'm not sure that is achievable. Same with quarterly. When is that applied? I suspect it might be possible to add more variations on the SUMPRODUCTs. For example, if the Factor is monthly and the column heading is equal to the year, multiply by (12-month). If the column heading is greater than the year, multiply by 12. So, that might be relatively easy ... take the Monthly SUMPRODUCT and have two versions of it, both with a Year comparison.

No idea how to work out a similar version for the Quarterly option.

I think you need to look at what is in the Summary List and check if all the options are available in the Categories List. Select cell A16 on the Expense Categories List and press F2 to go into Edit mode. What do you see? Do the same on cell A15 on the Summary sheet. Again, what do you see?

Sorry, only so much I can do with this. It might be doable but, you'd need a way of indicating the start quarter. Or you could calculate it:

Please Login or Register  to view this content.

Then same crack as with Monthly. Same year, Multiply by (4-Quarter). Future Years, Multiply by 4.

Have a go at it. Good way to get your head around the formula. It's not good to use a formula you are given and not understand it ... bad news in the future!

Regards, TMS

[TMS]

OK, pushing the limits here, but this seems to work

Please Login or Register  to view this content.

I've also fixed the Misc. entry in the Expense Category Drop Down List (it had a trailing space)


Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[JimBucklin]

Brilliant! This is amazing! Thank you so much. Thank you! Thank you!!

[TMS]

You're welcome. Thanks for the rep.

Just couldn't walk away from it The examples I checked looked right, hope the rest are.

I guess, if you think about it long enough, eventually you see the light.

Enjoy!


Regards, TMS

[JimBucklin]

I can't thank you enough! Now I just have to figure out how to copy all the "smart guy" stuff into the original sheet... I'll get to it later... Thanks again! I really appreciate all your help!

Cheers,

Jim