SUMIFS with LEFT as a reference

GarrettDyer

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!

Paul Newcome

Try this...

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

Paul Newcome

Try this...

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

GarrettDyer

@Paul Newcome Thanks for the help! I switched it up a little bit and was able to use FIND, but the @cell and @row were the fix for the issue.

Paul Newcome

Happy to help. 👍️