Forum Discussion
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
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
13 Replies
- 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
- '41007
How about add '（apostrophe）？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
Sueif 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)
Actually that works! Thanks! But I'd like to avoid having to add the apostrophe to a column of 100 codes.
- 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- Actually your first formula works for me. If MTCU is really text.
