pull Value Left 1 position from other smartsheet cell
Formula below works, however the "Discovery Receipt" cell on main sheet is a single select drop down:
0 - Bad
1 -Good
2 - Best
I only need to sum the left most 1st position.
=SUMIFS({Discovery Receipt}, {Date of Presentation}, @cell >= DATE(2020, 9, 1), {Date of Presentation}, @cell <= DATE(2020, 9, 30), {Status}, ="Completed", {AE/BDR}, ="Steve Kupp")
Below, gives me #incorrect argument set
=SUMIFS(VALUE(LEFT({Discovery Receipt}), 1), {Date of Presentation}, @cell >= DATE(2020, 9, 1), {Date of Presentation}, @cell <= DATE(2020, 9, 30), {Status}, ="Completed", {AE/BDR}, ="Steve Kupp")
What have I missed
Best Answers
-
You will need to put in another column in your Main Sheet which has the formula:
=LEFT([Discovery Receipt]@row,1)
then use that as the range in your formula:
=SUMIFS({Discovery Receipt left range}, {Date of Presentation}, @cell >= DATE(2020, 9, 1), {Date of Presentation}, @cell <= DATE(2020, 9, 30), {Status}, ="Completed", {AE/BDR}, ="Steve Kupp")
Make sure to update the names of columns and/or ranges above to their actual names...
-
=(VALUE(LEFT([Discovery Receipt]@row, 1))), was needed to add value to be counted on other sheet
Answers
-
You will need to put in another column in your Main Sheet which has the formula:
=LEFT([Discovery Receipt]@row,1)
then use that as the range in your formula:
=SUMIFS({Discovery Receipt left range}, {Date of Presentation}, @cell >= DATE(2020, 9, 1), {Date of Presentation}, @cell <= DATE(2020, 9, 30), {Status}, ="Completed", {AE/BDR}, ="Steve Kupp")
Make sure to update the names of columns and/or ranges above to their actual names...
-
i assume there is no way to just make the formula work. Is this because it is referencing another sheet?
-
=(VALUE(LEFT([Discovery Receipt]@row, 1))), was needed to add value to be counted on other sheet
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!