Sumifs with text and numbers
I have this issue
I am using a nested if-statement as a column formula where two of the values are sometimes text and other times saved as values. These are budget numbers so one example is 23001 & 23001F which are two different funds. I have tried both with and without CONTAINS, but the row for 23001 is either grabbing the value for 23001F (if set up as text ("23001) or nothing if set up as number (23001). And 23001F does not show up unless I use CONTAINS.
Here are my two versions of the formula:
=SUMIFS({Allocation}, {Fund}, Fund@row, @cell)......other statements )
=SUMIFS({Allocation}, {Fund}, Fund......other statements )
Answers
-
Not sure i follow the full story, but it seems like some of your 'funds' can be either stored as text or a number, the below concept should fix it for you, it is basically converting both values being evaluated into text and the comparing them.
SUMIFS({Allocation}, {Fund}, LEFT(@cell, LEN(@cell)) = LEFT(Fund@row, LEN(Fund@row)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!