Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

SUMIF on Numbers formatted as text

Copper Contributor

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

410078
4760018
5010037

 

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

41007x8
47600x18
50100x37

 

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

How about add '(apostrophe)?
Actually your first formula works for me. If MTCU is really text.

@Detlef LewinMaybe I'm not seeing something??

YeahMan_0-1704985266944.png

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

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

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

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

@YeahMan 

I meant this formula:

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

And the MTCU Codes are not text.

Check with ISTEXT().

 

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?

@YeahMan 

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.

 

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.

@YeahMan 

 

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

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

View solution in original post