CountIFS with Unique String

[Simon2001]

Hi

I'm trying to use a COUNTIFS on some data with multiple criteria. My source sheet has a list and each row has a different single or multiple code in the type column. On a separate sheet, I'm using the COUNTIFS to count and search the range for how many contain each unique code but I am stumped and its only counting the unique codes (single entry per cell) but I also want it to count them within in a cell with multiple code entries.

For example below, code 21-HR is counted twice but I want it to return 4 times as its also in two cells which have multiple values.

In the attached sheet, 21-HR is in rows 27 & 29 but also within rows 14 & 15.

The formula used is simply =COUNTIFS($B$4:$B$34,$C4) and for ease of review, I've left out the other criteria in the COUNTIFS.

Can anyone help?

 

[Peter_SSs]

U can try this.

With your posted sample data, try changing B1 to 1-HR

@Simon2001
For earlier version try this. Note that the formula should be confirmed with Ctrl+Shift+Enter, not just Enter

23 02 10.xlsm
	B	C	D
1
2
3	Risk Codes	Code	Count
4	22-HR	1-HR	0
5	32-HR, 22-HR	2-HR	1
6	22-HR, 12-HR	3-HR	0
7	12-HR	4-HR	0
8	2-HR	5-HR	0
9		6-HR	0
10		7-HR	0
11		8-HR	0
12		9-HR	0
13		10-HR	0
14		11-HR	0
15		12-HR	2
16		13-HR	0
17		14-HR	0
18		15-HR	0
19		16-HR	0
20		17-HR	0
21		18-HR	0
22		19-HR	0
23		20-HR	0
24		21-HR	0
25		22-HR	3
26		23-HR	0
27		24-HR	0
28		25-HR	0
29		26-HR	0
30		27-HR	0
31		28-HR	0
32		29-HR	0
33		30-HR	0
34		31-HR	0
35		32-HR	1
36		33-HR	0
37		34-HR	0
Count HR

Cell Formulas
Range	Formula
D4:D37	D4	=COUNT(SEARCH(" "&C4&","," "&$B$4:$B$100&","))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Simon2001]

Thanks for the help everyone!

 

[Peter_SSs]

You're welcome. Hopefully one or more of the suggestions will work for you.