Forum Discussion

Brass Contributor

Jan 10, 2024

Solved

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

excel

Formulas and Functions

YeahMan
Jan 11, 2024
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

peiyezhu

Bronze Contributor

Jan 11, 2024

'41007

How about add '（apostrophe）？

YeahMan
Brass Contributor
Jan 11, 2024
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
- peiyezhu
  Bronze Contributor
  Jan 13, 2024
  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)
YeahMan
Brass Contributor
Jan 11, 2024
Actually that works! Thanks! But I'd like to avoid having to add the apostrophe to a column of 100 codes.