Question related to sum(Text with Number)

[Excel]

Hello Everyone,

I want to sum text with number.

Like - 

IN COLUMN - 

IN ROW - 

The answer should be come in cell just written sheet with the help of formula

Please help.?

Here is an attached file

[#2]

@Excel 

Sum in column:

="USD "&SUMPRODUCT(--MID(B1:B6,5,100))

Sum in row:

="USD "&SUMPRODUCT(--MID(A1:F1,5,100))

[#3]

It works! Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

Sir, what is the -- ?? and where should i use?

[#4]

@Excel 

There are many roads that lead to Rome, here is another approach.

Formula inserted in the file.

=SUMME(RECHTS(B1:B6;FINDEN(" ";B1:B6)-1)*1)  in German

=SUM(RIGHT(B1:B6,FIND(" ",B1:B6)-1)*1) in English

=SOMME(DROITE(B1:B6;TROUVE(" ";B1:B6)-1)*1) in French

Contains array formula:

Do not enter the border {}, complete the formula with CTRL + SHIFT + RETURN!

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#5]

@Excel 

My approach:

[#6]

Thank you for giving the solution.
Sir can you please give a screenshot of the stepwise process. Because i am not able to solve it.. Please???

[#7]

Thank you so much sir.
Sir, why you put *1? Please explain

[#8]

@Excel 

I agree with @Patrick2788; the dollar amounts should never have been held as text in the first place.  Given that they are, in the days when I used non-array versions of Excel, I used Name Manager to bypass the fact that grid calculations mess with arrays.  If 'input' is the row or column of input data, then defining the named formula 'amount' to refer to

=VALUE(MID(input,4,10))

or

=--(MID(input,4,10))

will give a well-behaved array of numbers.

The result is then given by

= SUM(amount)

with no CSE or curly brackets required.  All that remains is to apply a number format such as

"USD" #,##0

[taking care to use the relevant parameter and thousands separators].

[#9]

Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

[#10]

Thank you so much for reply.

What is Input?

[#11]

It is a Name I defined to refer to the row or column of data to sum.
It can either be done by typing the Name into the box on the far left of the formula bar or by opening Name Manager (Ctrl/F3) and defining the new Names.

It provides an alternative way of referencing data to the A1 notation and works well in Excel (the last time I used a direct cell reference was in 2015)

[#12]

Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes: