Forum Discussion

Tracey Sansom's avatar
Tracey Sansom
Copper Contributor
Sep 16, 2018

formulas

Hello,

 

I was wondering (hoping really)

 

When using excel and you are wanting to calculate a row, is it possible to calculate the numbers even when there is text there too??

 

 

Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Sep 16, 2018

    Hi,

     

    You can add up a column of numbers even if there are some cells in that column have texts!

    This can be done by using SUM function as the below Example:

     

     

    The formula in cell A11 is this:

    =SUM(A1:A10)

     

    This is because the SUM function is programmed to ignore texts.

     

    I hope this helps you

    Regards

    • Tracey Sansom's avatar
      Tracey Sansom
      Copper Contributor
      Sep 16, 2018

      HI

       

      Thank you, but no this is what i meant sorry

      K1P2K7P2K12P1K12P2K7P2K12P1K12P2K7P2K1                      
      K2P3K3P3K2P10K3P10K2P3K3P3K2P10K3K10P2K3P3K3P2                  
      P1K3P2K1P2K3P2K9P3K9P2K3P2K1P2K3P2K9P3K9P2K3P2K1P2K3P1            
      P3K2P3K2P3K2P7K2P1K2P7K2P3K2P3K2P3K2P7K2P1K2P7K2P3K2P3K2P3          
      K2P2K2P1K2P2K3P2K6P2K1P2K6P2K3P2K2P1K2P2K3P2K6P2K1P2K6P2K3P2K2P1K2P2K2    
      P1K2P2K3P2K2K3P2K4P2K3P2K4P2K3P2K2P3K2P2K3P2K4P2K3P2K4P2K3P2K2P3K2P2K1    
      P2K2P2K1P2K2P2K3P2K3P2K3P2K3P2K3P2K2P2K1P2K2P2K3P2K3P2K3P2K3P2K3P2K2P2K1P2K2P2
      K1P2K2P3K2P2K2P3K2P1K2P5K2P1K2P3K2P2K2P3K2P2K2P3K2P1K2P5K2P1K2P3K2P2K2P3K2P1 

       

       

      Haytham Amairah wrote:

      Hi,

       

      You can add up a column of numbers even if there are some cells in that column have texts!

      This can be done by using SUM function as the below Example:

       

       

      The formula in cell A11 is this:

      =SUM(A1:A10)

       

      This is because the SUM function is programmed to ignore texts.

       

      I hope this helps you

      Regards

       

      • IngeborgHawighorst's avatar
        IngeborgHawighorst
        MVP
        Sep 16, 2018

        Hello,

         

        if that is your data, what do you want to calculate? Can you give some examples of the expected output and the logic that leads to the result?

         

        For example, if you want to sum the numbers for each column, you can use a formula that 

         

        - strips off the first character =mid(A1,2,4)

        - converts the result into a number =numbervalue(mid(A1,2,4))

        - applies that to more than one cell =numbervalue(mid(A1:A8,2,4))

        - and since that cannot be shown in a cell, feed this into the Sumproduct function to calculate the sum of all the values =SUMPRODUCT(NUMBERVALUE(MID(A1:A8,2,4)))

         

        In the following screenshot, this formula has been applied to cell A9 and copied across. If you want to do different calculations, please explain.

         

         

Resources