SUMIFS with Reference Columns?
Hi Everyone,
I am stuck on a scenario that I need assistance with.
I have a sheet to track project tasks. (screen # 1). In this sheet, I have a formula to calculate hours per person per month and it works great.
I would like to move this formula to a helper-sheet (screen # 2) so I can run reports on it.
However, when I attempt to run the same formula (and adjusting the column names), I can't get the formula to populate the information properly in the helper-sheet. Is there a restriction or a special way to run the SUMIFs formula with referenced columns?
Thank you in advance for any guidance.
Screenshot # 1 (Src Sheet)
Screenshot # 2 (Helper-Sheet)
Best Answer
-
You are setting up your cross sheet references incorrectly.
You have
[{Range}]:[{Range}]
You should have
{Range}
.
=SUMIFS(
[{Range}]:[{Range}], ..................=SUMIFS({Range}, ........................
Answers
-
It might be easier just to use an Index-match formula to mirror the result of the formula in your primary sheet.
=index({reference to actual hours worked column in primary sheet}, match([assigned to]@row, {reference to assigned to column in primary sheet}, 0))
I hope I'm understanding your desired result correctly and that helps. Let me know!
-
I appreciate the feedback Andy!
However, I am looking keep the Src (Project) sheet limited to specific columns (for template purposes) and use the helper-sheet to do all of my calculations and to generate reports.
I am surprised the formula isn't working in the helper-sheet as the only variable is that the columns are referencing data from another sheet... :-(
-
It might just be a syntax error. It seems like your Month and year formulas ought to have [Actual End Date]@cell rather than just @cell. If that doesn't work, I would test each individual piece of your formula to see which part is having the issue.
-
No. You need the @cell references in the MONTH and YEAR functions. That isn't the issue.
Exactly how are you bringing everything over into your helper sheet, and is there a reason you are bringing everything over and running your SUMIFS there as opposed to running your SUMIFS with cross sheet references?
-
I tried using cross sheet reference (preferred method) but unfortunatly I can't get it to work. The formula works in the original sheet but not in the helper sheet, see below:
Main Sheet: "Project Plan"
Helper Sheet:
Thank you in advance for your assistance.
-
You are setting up your cross sheet references incorrectly.
You have
[{Range}]:[{Range}]
You should have
{Range}
.
=SUMIFS(
[{Range}]:[{Range}], ..................=SUMIFS({Range}, ........................
-
Sorry, forgot to reply to this.
@Paul Newcome that did it, thank you for the assistance!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!