Apr 11 2021 07:34 AM - edited Apr 11 2021 07:35 AM
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
Apr 11 2021 07:59 AM
Sum in column:
="USD "&SUMPRODUCT(--MID(B1:B6,5,100))
Sum in row:
="USD "&SUMPRODUCT(--MID(A1:F1,5,100))
Apr 11 2021 08:05 AM - edited Apr 11 2021 08:07 AM
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?
Apr 11 2021 08:05 AM
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)
Apr 11 2021 08:20 AM
Apr 11 2021 08:24 AM
Apr 11 2021 08:39 AM
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].
Apr 11 2021 09:26 AM
Apr 11 2021 09:27 AM
Apr 11 2021 10:04 AM
Apr 11 2021 10:30 AM