Sumifs - Cross Sheet Reference Range Movement
I am working on a calculation where one sheet has month on month cost for a year in columns. i.e. Jan Cost, Feb Cost, Mar Cost etc. in sequential order.
In another sheet, I am doing some calculations to prepare the category wise costs based on the first sheet. For each month, all the conditions are same except for the sum column. I need to change Month Column range every time for each month manually.
Is there a way to automate this calculation where I need to make minimum changes.
Example Data Set:
I need to sum the cost for each month based on category in following format:
Appreciate your help here.
Thanks
Best Answer
-
Thank you for the clarification! Yes, each time you want to create a new column and reference this new column you will need to manually delete out the previous reference and add in a new one, selecting the new column.
If both sheets have all of their future columns created at the start, you could set up all these formulas in one go at the beginning, referencing empty columns... otherwise you would need to manually adjust this formula each moth and update the range.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
If you are doing this for the current month use =MONTH(TODAY()), it will return a number. You can nest this within IF() functions for each month to return a string, for example
=IF(MONTH(TODAY())=1, "Jan", IF(MONTH(TODAY())=2, "Feb"...
I'm not exactly sure what the current/desired functionality of your Sheets are, but if you are pulling monthly data in from one place to another you can use the MONTH() formula to only return data for the current month.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
I see that you marked @SoS | Dan Palenchar 's answer as not resolving your question. Would you be able to explain what didn't work for you?
Another way to do this, based on how your columns look to be set up, would be to use a Cross-Sheet SUMIF formula. This is how a SUMIF works:
=SUMIF({Column with Criteria}, Criteria, {column to SUM})
So for your instance:
=SUMIF({Category in Source Sheet}, Category@row, {Cost-Jan in Source Sheet})
You would then just need to update the final Range at the end of the formula per-column. You could have all the columns in either sheet set up at the beginning of the year, with the cross-sheet references in place to empty columns (which will originally have a SUM of 0, until new data is entered). Does that make sense? Or is that what you are already doing?
Here are some Help Articles you may find useful:
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P
Perhaps, I didn't make myself clear with my example.
This is the current formula I am using to sum the desired column.
=SUMIF({Condition Range}, <Condition>,{Sum Range})
Now, I have selected an entire column in {Sum Range} and this column is keeps on changing for each column but everything else remains fixed.
So I am seeking a way to just edit the one {Sum Range} part. I can select this {Sum Range} reference each time manually too.
-
Thank you for the clarification! Yes, each time you want to create a new column and reference this new column you will need to manually delete out the previous reference and add in a new one, selecting the new column.
If both sheets have all of their future columns created at the start, you could set up all these formulas in one go at the beginning, referencing empty columns... otherwise you would need to manually adjust this formula each moth and update the range.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!