Forum Discussion

YeahMan's avatar
YeahMan
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's avatar
    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

YeahMan's avatar
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's avatar
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)

Resources