Jan 26 2021 06:52 AM
Jan 26 2021 06:52 AM
For anyone familiar with the viral TikTok expense tracker, I am looking to apply this system using Microsoft Forms and Excel, there is an online tutorial on how to do it on YouTube, and a downloadable Google Sheet.
However, I am struggling a lot with the formula, the original tutorial for google sheets indicates that the formula should be:
=SUMIFS(Expenses!$H$1:$H, Expenses!$F$1:$F, ">="&$A2, Expenses!$F$1:$F, "<"&(EOMONTH($A2, 0)+1), Expenses!$I$1:$I, "="&B$1)
This is what I am trying to make work:
=SUMIFS(Expenses!$H$1:$H; Expenses!$F$1:$F>='Main Dashboard'!$A2; Expenses!$F$1:$F <&(EOMONTH('Main Dashboard'!$A2,0)+1); Expenses!$I$1:$I=&'Main Dashboard'!B$1)
But it doesn't seem to work, I am making my expense sheet available to be visualized via this link,
I would appreciate it if anyone has any suggestions.
Jan 26 2021 07:19 AM
@JRuedaSam The link isn't very useful as I can't edit or download it. Can you upload it here?
Your template looks very colourful, but if you can accept another format/lay-out, why not look into creating a pivot table on the basis of the expense table? No complicated formulae needed.
Jan 26 2021 07:42 AM
@Riny_van_Eekelen find the excel attached.
I understand the easier way is to create a pivot table from the original table, however, what I am looking to do is that it also marks when I go over budget, the categories on which I spend more/less, and overall to end up with an easy form that I just have to fill in and a formula that will do the rest for me. (like in the video)
If all of this is possible with a pivot table I am open to suggestions.
Jan 26 2021 08:11 AM - edited Jan 26 2021 08:26 AM
@JRuedaSam Have a look at the attached. In Sheet1, I added a pivot table. It doesn't look much now, because the data only has two items in it and all for January. But if you expand the data table the pivot table will expand with it. Just press Data / Refresh after adding expenses. And if you add budget info as well, you can create something that could work for you. But it might not look the same as what you have now.