Sumifs with text and numbers

Options
Marinajo
Marinajo ✭✭
edited 10/27/22 in Formulas and Functions

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Marinajo

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!