SOLVED

# SUMIF on Numbers formatted as text

Copper Contributor

# SUMIF on Numbers formatted as text

Hi,

I have something funky going on with my SUMIF statement. I have a table with a column of numeric codes which I have set the datatype to TEXT. My other table column has a count. I want to sum based on the first character of the code. E.g.,

MTCU Code                Number Entrants

 41007 8 47600 18 50100 37

I am getting the following for the SUMIF calculation:

SUMIF(Programs[MTCU Code],"4*",Programs[Number Entrants]) = 0.

BUT if I add an 'x' to the end of each code:

MTCU Code                   Number Entrants

 41007x 8 47600x 18 50100x 37

I get the correct result:

SUMIF(Programs[MTCU Code],"4*",Programs[Number Entrants]) = 26.

I really don't want to add the 'x' as I need this field for XLOOKUPs.

Can anyone assist?

Thanks,

Sue

13 Replies

# Re: SUMIF on Numbers formatted as text

Hi,
Thanks for the link. I tried the following:
=SUMIF(Programs[MTCU Code],CHAR(173)&"4*",Programs[Number Entrants])
and it is still returning 0

'41007

# Re: SUMIF on Numbers formatted as text

Actually your first formula works for me. If MTCU is really text.

# Re: SUMIF on Numbers formatted as text

@Detlef_LewinMaybe I'm not seeing something??

I've attached a small sample. Thanks for your help.

# Re: SUMIF on Numbers formatted as text

Actually that works! Thanks! But I'd like to avoid having to add the apostrophe to a column of 100 codes.

# Re: SUMIF on Numbers formatted as text

Hello I was given this code to break into text. Can someone convert it to text. mog mmm mmm moo gi mod mmm mmh mmo mot
best response confirmed by YeahMan (Copper Contributor)
Solution

# Re: SUMIF on Numbers formatted as text

Hi again,
Since I have 100's of these I found one way to mass insert an apostrophe is to use the VBA macro option found in this link: https://www.exceldemy.com/add-leading-apostrophe-in-excel/ since it was the only way I could get the prefix apostrophe that is hidden and is flagged as 'Number formatted as text' warning, which I want for XLOOKUPs.
I set the data type back to General, ran the macro on the column, and voila, the SUMIF works.

Thanks to everyone for their help
Sue

# Re: SUMIF on Numbers formatted as text

I meant this formula:

``SUMIF(Programs[MTCU Code],"4*",Programs[Number Entrants])``

And the MTCU Codes are not text.

Check with ISTEXT().

# Re: SUMIF on Numbers formatted as text

I don't understand. I've formatted the column as TEXT (highlighted yellow in the picture). If that doesn't work, what do you have to do in Excel to get numeric strings instead of numbers?

# Re: SUMIF on Numbers formatted as text

You have to format first and then enter the data.

If you enter data first and format later then you have press F2 and ENTER for each cell.

For a batch of cells in one column:

- Select cells.

- Goto Data -> Text to columns

- On step 3 choose "Text" and ENTER.

# Re: SUMIF on Numbers formatted as text

Lol, that seems unnecessarily fussy of Excel! Thanks for the info and steps. I didn't know about using 'Text to Columns' for this type of functionality.

# Re: SUMIF on Numbers formatted as text

if your raw data in b2:b4,

A2=encodeurl(

"cli_tb~sht~" & arraytotext(b2:c4,1) & ";select sum(f02) from sht where f01 like '4%';")

D2

=WEBSERVICE("https://e.anyoupin.cn/eh3/?sql~" & A2)

1 best response

Accepted Solutions
best response confirmed by YeahMan (Copper Contributor)
Solution

# Re: SUMIF on Numbers formatted as text

Hi again,
Since I have 100's of these I found one way to mass insert an apostrophe is to use the VBA macro option found in this link: https://www.exceldemy.com/add-leading-apostrophe-in-excel/ since it was the only way I could get the prefix apostrophe that is hidden and is flagged as 'Number formatted as text' warning, which I want for XLOOKUPs.
I set the data type back to General, ran the macro on the column, and voila, the SUMIF works.

Thanks to everyone for their help
Sue