formulas

Question

Hello,&nbsp;I was wondering (hoping really)&nbsp;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??&nbsp;&nbsp;

ingeborghawighorst · Answer

Hello,
&nbsp;
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?
&nbsp;
For example, if you want to sum the numbers for each column, you can use a formula that&nbsp;
&nbsp;
- 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&nbsp;=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&nbsp;=SUMPRODUCT(NUMBERVALUE(MID(A1:A8,2,4)))
&nbsp;
In the following screenshot, this formula has been applied to cell A9 and copied across. If you want to do different calculations, please explain.
&nbsp;

&nbsp;

haytham amairah · Answer

Hi,&nbsp;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:&nbsp;&nbsp;The formula in cell A11 is this:=SUM(A1:A10)&nbsp;This is because the SUM function is programmed to ignore texts.&nbsp;I hope this helps youRegards

tracey sansom · Answer

HI&nbsp;Thank you, but no this is what i meant sorryK1P2K7P2K12P1K12P2K7P2K12P1K12P2K7P2K1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;K2P3K3P3K2P10K3P10K2P3K3P3K2P10K3K10P2K3P3K3P2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;P1K3P2K1P2K3P2K9P3K9P2K3P2K1P2K3P2K9P3K9P2K3P2K1P2K3P1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;P3K2P3K2P3K2P7K2P1K2P7K2P3K2P3K2P3K2P7K2P1K2P7K2P3K2P3K2P3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;K2P2K2P1K2P2K3P2K6P2K1P2K6P2K3P2K2P1K2P2K3P2K6P2K1P2K6P2K3P2K2P1K2P2K2&nbsp;&nbsp;&nbsp;&nbsp;P1K2P2K3P2K2K3P2K4P2K3P2K4P2K3P2K2P3K2P2K3P2K4P2K3P2K4P2K3P2K2P3K2P2K1&nbsp;&nbsp;&nbsp;&nbsp;P2K2P2K1P2K2P2K3P2K3P2K3P2K3P2K3P2K2P2K1P2K2P2K3P2K3P2K3P2K3P2K3P2K2P2K1P2K2P2K1P2K2P3K2P2K2P3K2P1K2P5K2P1K2P3K2P2K2P3K2P2K2P3K2P1K2P5K2P1K2P3K2P2K2P3K2P1&nbsp;&nbsp;&nbsp;Haytham Amairah&nbsp;wrote:Hi,&nbsp;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:&nbsp;&nbsp;The formula in cell A11 is this:=SUM(A1:A10)&nbsp;This is because the SUM function is programmed to ignore texts.&nbsp;I hope this helps youRegards&nbsp;

haytham amairah · Answer

Hi,&nbsp;I advise you to try the solution provided by&nbsp;IngeborgHawighorst, it also works for a grid of cells such as A1:C8 as follows:=SUMPRODUCT(NUMBERVALUE(MID(A1:C8,2,4)))&nbsp;Regards

Forum Discussion

formulas

4 Replies

Resources