 # Question related to sum(Text with Number)

Frequent Contributor

# Question related to sum(Text with Number)

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

Here is an attached file

11 Replies

# Re: Question related to sum(Text with Number)

Sum in column:

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

Sum in row:

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

# Re: Question related to sum(Text with Number)

It works! Thank you so much sir

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

# Betreff: Question related to sum(Text with Number)

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)

# Re: Question related to sum(Text with Number)

My approach: # Re: Question related to sum(Text with Number)

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???

# Betreff: Question related to sum(Text with Number)

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

# Re: Question related to sum(Text with Number)

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].

# Re: Question related to sum(Text with Number)

Thank you so much sir

# Re: Question related to sum(Text with Number)

Thank you so much for reply.

What is Input?

# Re: Question related to sum(Text with Number)

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)

# Re: Question related to sum(Text with Number)

Thank you so much sir