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
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!