Snasui Excel Forum

Hi Dear,

I need your input how to extract a currency partially and sum values base on that currencies.

I attached a sample with result in highlight cells

Best Wishes

..

C2

=IFERROR(INDEX(IFERROR(LEFT($B$2:$B$9,SEARCH(" ",SUBSTITUTE($B$2:$B$9,"$"," "))-1),"CNY"),AGGREGATE(15,6,ROW($A$2:$A$9)/(MATCH(IFERROR(LEFT($B$2:$B$9,SEARCH(" ",SUBSTITUTE($B$2:$B$9,"$"," "))-1),"CNY"),IFERROR(LEFT($B$2:$B$9,SEARCH(" ",SUBSTITUTE($B$2:$B$9,"$"," "))-1),"CNY"),0)=ROW($A$1:$A$9)),ROW(A1))-1),"")

Ctrl + Shift + Enter


D2

=IFERROR(IF(C2="CNY",SUM($B$2:$B$9),SUM(--IFERROR(SUBSTITUTE(SUBSTITUTE(IF(SEARCH(C2,$B$2:$B$9),$B$2:$B$9),C2&"$",""),C2&" ",""),0))),"")

Ctrl + Shift + Enter


I suggest about in rows 7,8 should entry in the same way of the currency format such as UK, HK, EUR, etc.

Norkaz

Another example,

1. C2
   =IFERROR(SUBSTITUTE(LEFT(TEXT(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$9)/(0<FREQUENCY(MATCH(LEFT($B$2:$B$9,3),INDEX(LEFT($B$2:$B$9,3),0),0),ROW($B$2:$B$9)-ROW($B$2)+1)),ROWS(C$6:C6))),"""CNY""0;""CNY""-0;;@"),3),"$",""),"")
   Enter > Copy down
2. D2
   =SUMPRODUCT(--ISNUMBER(SEARCH(C2,TEXT($B$2:$B$9,"""CNY""0;""CNY""-0;;@"))),--MID(TEXT($B$2:$B$9,"""CNY""0;""CNY""-0;;@"),4,10))
   Enter > Copy down

Thank you both so much