pull Value Left 1 position from other smartsheet cell

Richard Zombek

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

Leibel S

@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

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

Leibel S

@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

i assume there is no way to just make the formula work. Is this because it is referencing another sheet?

Richard Zombek

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