SUMIFS Formula
I am trying to create a SUMIFS formula referencing another sheet: =SUMIFS({Sheet - PTO & Project Staffing Range 2}, {Sheet - PTO & Project Staffing Range 1}, Projects@row, {Sheet - PTO & Project Staffing Range 3}, "Trainee Solicitor")
The column I am trying to add is a formula so, I have tried VALUE in front of the above formula but it isn't bringing back a value (I'm expecting 42 to come back).
If I create a SUMIF formula, excluding the last criteria for "Trainee Solicitor" it works fine so, I'm a bit stumped at what I'm doing wrong?
Thanks
Cheryl
Best Answer
-
Thank you for these screen captures, this helps a lot! The formula will be evaluating the criteria in each individual row, regardless of the rows above or below.
This means that the correct answer to your formula is 0, since there are no rows that say "Rushmoor" AND also say "Trainee Solicitor" in the same row. The cell in your Project column is blank wherever it says "Trainee Solicitor".
You'll want to either add a formula into these blank cells that return the Parent:
=PARENT()
Or you could have a helper column with a column formula applied, then hide this column in your sheet.
=PARENT([Project]@row)
Then adjust your first criteria range {Sheet - PTO & Project Staffing Range 1} to look at this helper column for the Parent name instead of the Project column. Does that make sense?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Can you post a screen capture of the sheet you're looking into, identifying the Column that's your "Range 3" and column type? (But please block out sensitive data). It would also be helpful to know how this formula is not working, are you receiving an error or an incorrect result?
If the result is incorrect, it may be that the formula cannot find a match in your Staffing Range 3 column to the exact words "Trainee Solicitor". Check to ensure that the text in that column is spelled the same way as what you have in your formula.
Additionally, if the column of Range 3 is Multi-Select and this is one of many options, you'll want to use the HAS function:
=SUMIFS({Sheet - PTO & Project Staffing Range 2}, {Sheet - PTO & Project Staffing Range 1}, Projects@row, {Sheet - PTO & Project Staffing Range 3}, HAS(@cell, "Trainee Solicitor"))
Let me know if any of this has helped!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P
I am getting an incorrect result, I'm expecting to get a return of 35 against the project but am getting a 0.
I have just tried your formula including the HAS clause, as you are correct in your assumption, that range 3 is a drop down of options (Single option).
As you can see against the project Rushmoor I'm expecting a return of 35 but am getting a zero against Trainee
Thanks
Cheryl
-
Thank you for these screen captures, this helps a lot! The formula will be evaluating the criteria in each individual row, regardless of the rows above or below.
This means that the correct answer to your formula is 0, since there are no rows that say "Rushmoor" AND also say "Trainee Solicitor" in the same row. The cell in your Project column is blank wherever it says "Trainee Solicitor".
You'll want to either add a formula into these blank cells that return the Parent:
=PARENT()
Or you could have a helper column with a column formula applied, then hide this column in your sheet.
=PARENT([Project]@row)
Then adjust your first criteria range {Sheet - PTO & Project Staffing Range 1} to look at this helper column for the Parent name instead of the Project column. Does that make sense?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
-
No problem at all! I'm glad we could figure it out. 🙂
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.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!