SUMIFS with LEFT as a reference

I'm looking to sum a range as long as 3 criterion are met. 1 of those criterion is that Left of another sheet reference (4 characters) match a cell (could be text in the formula as well).

=SUMIFS({Time To Assemble}, {IPK}, [Machine Type]@row, {Progress}, "Finished", LEFT({Item Number}, 4), [Machine Type]$17)

This is currently what I have and it's returning invalid data. After some research I believe it has to do with SUMIFS looking for the exact text in the cell and not able to reference an entire row with the left function.

Any thoughts would be helpful!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!