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
Check out the Formula Handbook template!