Sumproduct with this data? Values with text

[JumboCactuar]

Hi,
I have data in the following format and want to sumproduct but not sure how to trim out the text

12,29 may also need changing to 12.29

Cost	Quantity
EUR 12,29	2
EUR 11,05	6
EUR 11,15	3
EUR 11,15	1
EUR 2,16	2
EUR 5,50	2
EUR 5,50	1
EUR 5,50	4
EUR 13,97	25
EUR 16,92	2
EUR 27,98	6
EUR 31,97	1

Expected result should be
761.24

Formula only possible?

Any help appreciated

 

[Peter_SSs]

Like this?

20 11 05.xlsm
	A	B	C	D
1	Cost	Quantity
2	EUR 12,29	2		761.24
3	EUR 11,05	6
4	EUR 11,15	3
5	EUR 11,15	1
6	EUR 2,16	2
7	EUR 5,50	2
8	EUR 5,50	1
9	EUR 5,50	4
10	EUR 13,97	25
11	EUR 16,92	2
12	EUR 27,98	6
13	EUR 31,97	1
SUMPRODUCT

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--SUBSTITUTE(MID(A2:A13,5,20),",","."),B2:B13)

 

[JumboCactuar]

@Peter_SSs

Works thankyou

But 1 issue

If my data ends at row 117 for example and I use

=SUMPRODUCT(--SUBSTITUTE(MID(A2:A10000,5,20),",","."),B2:B10000)

It outputs as #Value

How would I make it always work to last row?

 

[Peter_SSs]

=SUMPRODUCT(--SUBSTITUTE(0&MID(A2:A10000,5,20),",","."),B2:B10000)

 

[JumboCactuar]

Perfect
Thanks for the help

 

[Peter_SSs]

Perfect
Thanks for the help

You're welcome. Thanks for the confirmation.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)