Forum Discussion
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...
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
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
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.
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?