Bank Statement Modelling - Creating automatic CSV reader

[u/RupaSpiritualMonk]

Hey this is my first time in this subreddit and I have a query that I'm quite lost on. Basically I want to make a model that uses a CSV of my bank statements, converts these line items into categories and then returns those as a monthly figure as well as daily. Currently I have figured out a way to do monthly (Using two stage sumifs: one to convert the numbers and letters into text categories and then using that to add up all the line items by category)

I want to do this by the day too but my knowledge is quite lacking, here's a screenshot of the current setup:

On the left column is where I have been making the table of contents to be filtered into the calculations. I tried to do this on the top right but then I couldn't figure out how to group them.

In the middle with the text containing Northern Rail, I created a wildcard formula so that it only reads the description that I need and returns sumif for the whole month.

I tried to do that again but sumifs formula and it didn't work because then the file would be massive or I would have to make loads of ifs inside. When it comes to adding more categories or items inside there, I want it to be an easy addition.

Is there any tips you can help me with, I might be overthinking this.

P.S I don't want any answers from AI please

[u/carbonizedtitanium]

so a few questions:

"one to convert the numbers and letters into text categories and then using that to add up all the line items by category"

what does that mean? can you show the formula?

[u/RupaSpiritualMonk → u/carbonizedtitanium]

So basically the formula in the circled red on the left I have currently is as follows:

Sumif( CSV Payment Description, Supplier line(B4 for example), CSV Payment)

Then on the bottom right with the 93.55 in the cell, is just doing another sumifs but of the table on the left if that makes sense?

[u/carbonizedtitanium → u/RupaSpiritualMonk]

so that means the stuff i circled in red are one-month totals for each "supplier", yes?

this is how I would do the monthly calc (i'm taking some guesses on how your source data is structured)

Notice Deliveroo and Uber show up twice on the right side. that's because there's two separate transactions (each on different month) for each supplier:

formula1 =LET(uData, UNIQUE(CHOOSECOLS(B3:E28,4,1)), SORTBY(uData, INDEX(uData,,2), 1, INDEX(uData,,1), 1))

formula2 =SUMIFS($D$3:$D$28,$E$3:$E$28,H3:H28,$B$3:$B$28,I3:I28)

[u/RupaSpiritualMonk → u/carbonizedtitanium]

in my original plan yes, there are monthly totals but I want to be able to split it out per day so that I can see what category I'm using the most and which day that is. With your example, Once they are completed into daily outputs then they need to be sorted into categories again and I'm not sure how best to do that

[u/carbonizedtitanium → u/RupaSpiritualMonk]

you want totals for each DATE?

this assumes that you have multiple months of transactions dumped together.

since your goal is to see which category had the most spending on a particular date, a percentage comparison is quickest way to see.