Viral TikTok expense tracker for excel and microsoft forms

Copper Contributor

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. 

 

Regards, 

JRS 

7 Replies

@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.

@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. 

 

Thanks. 

JRS 

@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.

I can definitely understand why you're struggling with the formula, it can be confusing. Have you tried reaching out to the person who created the tutorial on YouTube? They may be able to give you some more guidance. Also, if you're looking for other resources, this website has some great information on promoting your social media accounts that you might find helpful. And if all else fails, you can always reach out to the community for help! I'm sure someone here has experience with SUMIFS and would be happy to lend a hand. Good luck with your expense tracker! Hope it works out for ya.

I totally understand the struggle of trying to make formulas work in Excel, especially when trying to adapt them from other programs.

Have you tried using semi-colons instead of commas in your formula? That's usually the case when working with Excel in non-English languages. Also, have you double-checked that the cell references are correct? It's easy to accidentally refer to the wrong cells and end up with errors. I checked out your expense sheet and it looks great! Good luck with your project. By the way, have you considered promoting your TikTok expense tracker on social media and buy tiktok likes to increase your reach? It could be a great way to share your work with others who might find it helpful!

@JRuedaSamThe first thing that I see is that you have Google Sheet notation in your formula. Google Sheets uses the format A2:A to indicate start at A2 and use the rest of column A but Excel does NOT support that notation. I haven't looked at your attachment but you need to either define the range like A2:A100 or use the whole column A:A or define the range using a name or by formatting as Table (I recommend this) and use the table reference like Table1[columnName]

EDIT: So I found a few other errors in the formula including some basic format.  I changed the semi-colons to commas (I'm in the USA) but also the format for the SUMIFS uses the conditional a value then the condition in a text format:

 

 

=SUMIFS(Expenses!$H$1:$H100, 
        Expenses!$F$1:$F100, ">="&'Main Dashboard'!$A2, 
        Expenses!$F$1:$F100, "<"&(EOMONTH('Main Dashboard'!$A2,0)+1), 
        Expenses!$I$1:$I100, "="&'Main Dashboard'!B$1)

 

 

so notice above how each of the comparators like >= and < are inside quotes and then concatenated with the value to be used.  Note, this is the SAME as Google Sheets and the original Google Sheet formula did have it correctly formatted there.

see attached 

Edit2: I updated the formula to use the Table notation:

=SUMIFS(Tabla1[Total Amount(Use ''.'' instead of '','' before decimals)],
        Tabla1[Purchase date], ">="&$A2,
        Tabla1[Purchase date], "<"&(EOMONTH($A2,0)+1),
        Tabla1[Category],"="&B$1)

notice how the references to the "Expenses" tab now refer to the Tabla1 and the corresponding column name.  It makes it a little more readable and will refer exactly to the table column size and note the entire column or miss data because you use F1:F100 but the data now goes to 200.

I attached EXPENSES-table to show that example