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

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jan 11, 2024
Actually your first formula works for me. If MTCU is really text.
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jan 11, 2024

    YeahMan 

    I meant this formula:

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

    And the MTCU Codes are not text.

    Check with ISTEXT().

     

    • YeahMan's avatar
      YeahMan
      Brass Contributor
      Jan 11, 2024
      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?
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Jan 11, 2024

        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.

         

Resources