Sum If Cell Contains a certain letter

[Tashat]

Hi All

I would be very grateful for your help on the following issue. I have a list of staff working in different regions. Some are full time, others half time (0.5) etc and I want to sum up the total number of staff working in each region. However, some work in multiple regions as indicated by the letters A, B, C and D in Column A.

I want to find the total for Regions A - D as shown in E3:H4 using a formula that adds up column B IF column A contains a letter A for example. At the moment the totals are hard-typed in E4:H4. I've tried SUMPRODUCT and SUMIFS, but can't allow for a wildcard function. I would prefer to avoid using Macros if possible.

Book2
	A	B	C	D	E	F	G	H
1	Region	Number of Staff
2	A	1
3	A	1			A	B	C	D
4	A	1			15.93	9.43	11.53	9.5
5	A	1
6	A	1
7	A	1
8	A	1
9	A	1
10	A	1
11	A	1
12	A	1
13	A	0.6
14	A	1
15	A & B	0.5
16	A & B	0.5
17	A & B	0.5
18	A & B	0.5
19	B	0.6
20	B	1
21	B	1
22	B	1
23	B & A	0.5
24	B & A	0.5
25	B & A & C	0.33
26	B & C	0.5
27	B & C	0.5
28	C	1
29	C	0.6
30	C	0.6
31	C	1
32	C	1
33	C	1
34	C	1
35	C	1
36	C & B	0.5
37	C & B	0.5
38	C & B	0.5
39	D & C	0.5
40	D & C	0.5
41	D & C	0.5
42	D	1
43	D	1
44	D	1
45	D	1
46	D	1
47	D	1
48	D	1
49	D	1
Sheet1

 

[Fluff]

How about

=SUMIFS($B:$B,$A:$A,"*"&E3&"*")

 

[Tashat]

How about

=SUMIFS($B:$B,$A:$A,"*"&E3&"*")

Thank you again! Perfect solution as always!

 

[Fluff]

You're welcome & thanks for the feedback.