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
-
Try this...
=SUMIFS({Time To Assemble}, {IPK}, [Machine Type]@row, {Progress}, "Finished", {Item Number}, LEFT(@cell, 4), [Machine Type]$17)
Answers
-
Try this...
=SUMIFS({Time To Assemble}, {IPK}, [Machine Type]@row, {Progress}, "Finished", {Item Number}, LEFT(@cell, 4), [Machine Type]$17)
-
@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.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!