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
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 https://support.office.com/en-us/article/SUM-function-043E1C7D-7726-4E80-8F32-07B23E057F89 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

 

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Sep 16, 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