Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUM IF & LEFT @cell

edited 12/09/19 in Archived 2017 Posts

I keep getting the result of 0 angry

I am trying to sum a range if another column contains "Street Lighting"

I have tried changing the text reference from the entire column, to particular range and adding the LEFT @cell with and without #(5) see below.

=SUMIF(Team:Team, LEFT(="Street Lighting", [Days to respond]2:[Days to respond]4))

=SUMIF(Team6:Team45, LEFT(@cell, 5) = "Street Lighting", [Days to respond]2:[Days to respond]4)

Can someone please advise what I am doing wrong?

Thank you 


  • I'm not sure exactly what answer you want.  But I can see a few errors in the formula.  The Days to respond rows (rows 2-4) don't line up with the Team rows ( rows 6-45).  And if you want the criteria to be equal to the left 5 letters, then you need to use "Stree" (only 5 letters).

    Here's what I thought you were going for.

    =SUMIF(Team6:Team45, LEFT(@cell, 5) = "Stree", [Days to respond]6:[Days to respond]45)

    Good Luck


This discussion has been closed.