Need to get a total from an array based on corresponding comma separated cells

[TheEncourager]

Hi all,

I need to get a total based on a value in a set of comma separated cells:

So I would need the total from column B of cells that have the desired week (say W27) in column A. Is there any formula that can help achieve this?


Thanks in advance!!

 

[Radoslaw Poprawski]

provide minisheet please

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B
1
2	W28;W29;	1
3	W1;	1
4	W27;W28;W11;	0.5
5
6
7
8	W27	0.5
9	W28	1.5
10	W1	1
Main

Cell Formulas
Range	Formula
B8:B10	B8	=SUMIFS($B$2:$B$4,$A$2:$A$4,"*"&A8&";*")