Using SUMIFS to add various categories

[howard]

I have tried to add the various categories of Staff -see C14 to C18, but the formula returns zero


See my sample data below


It would be appreciated if someone could kindly assist me

Excel 2012
	B	C
1	Clerical- Admin	12
2	Supervisors- Service	3
3	Technicians- Service	5
4	Apprentices- Service	4
5	Salesmen- N/V	6
6	Salesmen- U/V	3
7	Salesmen- Parts	3
8	General- Admin	1
9
10
11	Total	37
12
13
14	Admin	0
15	Service	0
16	N/V	0
17	U/V	0
18	Parts	0
19
20
21	Total	0
Sheet1

Cell Formulas
Range	Formula
C11	=SUM(C1:C10)
C14	=SUM(SUMIFS(C1:C13,B1:B13,"?*"&B14&""""))
C21	=SUM(C14:C20)

 

[FormR]

Hi, maybe for C14 copied down.

=SUMIFS($C$1:$C$13,$B$1:$B$13,"*"&B14)

 

[Weazel]

If the criteria is always going to be to the right with text before it you could try...

Excel 2013

	B	C
1	Clerical- Admin	12
2	Supervisors- Service	3
3	Technicians- Service	5
4	Apprentices- Service	4
5	Salesmen- N/V	6
6	Salesmen- U/V	3
7	Salesmen- Parts	3
8	General- Admin	1
9
10
11	Total	37
12
13
14	Admin	13
15	Service	12
16	N/V	6
17	U/V	3
18	Parts	3
19
20
21	Total	37

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula C14 =SUMIF($B$1:$B$8,"*"&B14,$C$1:$C$8) <tbody> </tbody>

<tbody>
</tbody>

 

[howard]

Thanks for the help, Guys