Sumifs with text and numbers

Marinajo

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 )

Leibel S

@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)))