Hi.
Is it possible to build a formula in excel to sum the last 6 months numbers on a different worksheet and next month it rolls forward so it again captures the last 6 months?
In my attachment, (Summary tab, cell H7) I want to sum the figures in the raw tab for past 6 months. I.e I9+H9+G9+F9+E9+D9
Can this be automated via way of a formula or can only be done via VBA?? I know how to sum the cells, but just want to know if this can be automated so the formula automatically rolls forward.
Probably be easy enough if all the dates and figures were on the same rows. Split as you have them, not so much so.
Where do you put the date you want the 6 month calculation based on?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Haven't put a date as wasn't sure if it was required. Feel free to add a date wherever you want.
It isn't if we use TODAY() as the date, but this might not give exactly the results you expect.
Is a change in layout possible?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes nothing is fixed. Change as you wish.
But when does the calculation have to run from? Today or the first of this month, or the last day of last month? These are important questions that you need to answer, please.
That's not really the way this works. And you haven't commented on the fact that you have made a rod for your own back by splitting the dates and figures.Feel free to add a date wherever you want.
Probably easier to put them in, ideally, a single pair of columns or, less ideal, a single pair of rows. I'd recommend columns as you can AutoFilter the rows whereas you can't AutoFilter columns.
As you're using 365, filtering is less of a problem as you can use the Filter function.
Please restructure your sample and repost it.
yeah sorry. Should have been more specific. Last day of the month last month. So 31st Jan.
Ok. I've now arranged the dates and the values into columns for ease of use on the raw tab.
I've also put the date I wish to work from in n3 on the summary worksheet
Last edited by AliGW; 02-14-2022 at 10:18 AM. Reason: PLEASE don't quote unnecessarily!
All in one:
=SUMIFS(Raw!$D$2:$D$23,Raw!$C$2:$C$23,">="&EOMONTH(TODAY(),-8)+1,Raw!$C$2:$C$23,"<="&EOMONTH(TODAY(),-1))
PL see file. I have rearranged data in RAW sheet. Is it Ok.
IN H7
Formula:
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Yet another option:Formula:Please Login or Register to view this content.
Thanks for the rep.
Ditto. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks