Unable to add alphanumeric text..........

[Shweta]

Hi All,

I have a spreadsheet containing the data like this. I want to add it.

column A
32K
45k
47k
385k
100k

It is not being possible to add all this text with sum function. I tried it with sumproduct as well, but still didn't get the answer. Answer should be 609.

Can anybody please help me out?

Thanks.

 

[dcamos]

Assuming your range is in A8 to A12 this should work.

{=SUM(LEFT(A8:A12,FIND("k",A8:A12)-1)*1)}

Make sure to enter it with Ctrl+Shift+Enter instead of just Enter.

 

[Ron Coderre]

with your sample data in A1:A5,
this regular formula returns the sum of the numeric values

B1: =SUMPRODUCT(--SUBSTITUTE(UPPER(A1:A5),"K",""))

Is that something you can work with?

 

[T. Valko]

Hi All,

I have a spreadsheet containing the data like this. I want to add it.

column A
32K
45k
47k
385k
100k

It is not being possible to add all this text with sum function. I tried it with sumproduct as well, but still didn't get the answer. Answer should be 609.

Can anybody please help me out?

Thanks.

Here's another one.

Assuming every cell entry contains the letter K at the end.

Array entered**:

=SUM(--LEFT(A2:A6,LEN(A2:A6)-1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Ron Coderre]

I know how much you like using array formulas, T.
But, why not use this regular formula?:

=SUMPRODUCT(--LEFT(A1:A5,LEN(A1:A5)-1))

 

[T. Valko]

I know how much you like using array formulas, T.
But, why not use this regular formula?:

=SUMPRODUCT(--LEFT(A1:A5,LEN(A1:A5)-1))

Yeah, that's yet another example.

 

[Shweta]

Hi Dcamos,

Thanks for your quick reply. Your formula is working properly, but I am not getting the logic why we added *1 at the end of this formula?

 

[Shweta]

Thanks All...

All the formulaes are working properly.

I appreciate your help!

Thanks a lot!

 

[T. Valko]

Thanks All...

All the formulaes are working properly.

I appreciate your help!

Thanks a lot!

You're welcome. Thanks for the feedback!