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
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!