to define a custmized function to add values in cells seperted by comma

[SachinLone]

Hello</SPAN>
I always get daily data in following format with last 2 characters </SPAN>fixed for quantity.</SPAN>
Column A Column B</SPAN>

New York Apple_20, Orange_15,Pineapple_05
London Orange_22,Pineapple_20
Paris Pineapple_07
Tokyo </SPAN>

New York Apple_20
London Orange_15, Apple_20, ,Pineapple_12
Paris Apple_11, Orange_15,Pineapple_15
Tokyo Pineapple_12 Apple_11, Orange_15,</SPAN>

New York Orange_22,Pineapple_20
London Pineapple_17
Paris
Tokyo Apple_20</SPAN>

I write the data from excel to paper and sort it.
I make table with city and fruit consumption manually at cell A200</SPAN>

[TABLE="width: 277"]
<TBODY>[TR]
[TD]cell A200</SPAN>[/TD]
[TD]Apple</SPAN>[/TD]
[TD]Orange</SPAN>[/TD]
[TD]Pineapple</SPAN>[/TD]
[/TR]
[TR]
[TD]New York</SPAN>[/TD]
[TD]cell B201[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]London</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Paris</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tokyo</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL span=4></COLGROUP>[/TABLE]

I cannot use filter or pivot data.
I tried to used function like vlookup, SUMIFS , RIGHT , LEN etc
I badly need help to define customized VBA functions in cells like B201 to sum quanitity of apple in column B for new york etc

Regards
Sachin</SPAN>

 

[kvsrinivasamurthy]

In B201 use this array formula and drag down.

=sum(if(isnumber(find(b$200,$b$2:$b$15))*($a$2:$a$15=$a201),value(mid($b$2:$b$15,find(b$200,$b$2:$b$15)+len(b$200)+1,2)),0))

After pasting formula
Press F2
Press Ctrl+Shift+Enter
You will see {} around Formula.