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
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!