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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Richard Zombek

    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...

  • Richard Zombek
    Answer ✓

    =(VALUE(LEFT([Discovery Receipt]@row, 1))), was needed to add value to be counted on other sheet

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Richard Zombek

    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?

  • Richard Zombek
    Answer ✓

    =(VALUE(LEFT([Discovery Receipt]@row, 1))), was needed to add value to be counted on other sheet

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!