Forum Discussion
Formula help please!
Hi everyone,
I need some help with some formulas, I will just start off with the one...
In my reconciliation tab I need to grab the sum of transactions from a statement tab, granted they match the particular category I have given them in the statement tab. The current formula I have, which works, is:
=SUMIF('FY24 Statements'!$H$2:$H$5,B12, 'FY24 Statements'!$E$2:$E$5)
The issue is that with each month I am making this a running statement tab, so I am adding in the new data each month at the top which means I need to change the ranges manually for the new month. I feel like there would be a more automated way around this so I was wondering if there was a way I could do a SUMIF that incorporates the month and year that I have displayed on the first tab (cell B2). So basically, it will only sum the amounts (FY24 Statements'!$E$2:$E$5) if the month and year match cell B2 AND then only sum from the category (FY24 Statements'!$H$2:$H$5).
Does this make sense??
Hope someone can help, thanks!
Megan
1 Reply
mogollen I made mock-up based on your description and suggest you transform the transactions list into a structured Excel table. Then you can refer to the table/column name without the need to know where the table sits in the workbook. And then just add new transaction at the bottom of the table. It will automatically expand and so will the references that you have in the SUMIF. Use SUMIFS if you want to include the dates as well.
It's demonstrated in the attached file. See if you can apply this to your own. If you are using Excel fro MS365 or 2021 you can use the 'all-in-one' formula. Otherwise, use the 'one-by-one' solution.
