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

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.

     

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Sep 17, 2018

    Hi,

     

    I advise you to try the solution provided by IngeborgHawighorst, it also works for a grid of cells such as A1:C8 as follows:

    =SUMPRODUCT(NUMBERVALUE(MID(A1:C8,2,4)))

     

    Regards

Resources