COUNT specific text within cells split by delimiter

[JoshLyman]

I have an MS form which populates the below sheet/table.

Columns E, F, and G ask for people can to tick one or several items from a given list. If the tick several, the cell contains all items, separated by a semi-colon as a delimiter.

Is there a way, via my second table, to count how many times a specific KSB has been ticked on the form, given that a given cell may contain several, rather than one cell per KSB?

Off-the-job submissions log (2).xlsx
	A	B	C	D	E	F
1	Student registration number	Date of off-the-job activity	Duration of off-the-job activity (hours)	Please tick the appropriate Knowledge elements covered by this activity	Please tick the appropriate Skills elements covered by this activity	Please tick the appropriate Behaviour elements covered by this activity
2	A1234567	11/10/2023	4.5	K1: How to shape organisational mission, culture and values.;K2: Organisation structures; business modelling; diversity; global and horizon scanning perspectives; governance and accountability; technological and policy implications.;	S5: Lead and respond in a crisis situation using risk management techniques.;	B4: Value difference and champion diversity.;
3	A1234567	11/13/2023	1.5	K1: How to shape organisational mission, culture and values.;K2: Organisation structures; business modelling; diversity; global and horizon scanning perspectives; governance and accountability; technological and policy implications.;K3: New market strategies, changing customer demands and trend analysis.;	S5: Lead and respond in a crisis situation using risk management techniques.;S6: Act as a Sponsor/Ambassador, championing projects and transformation of services across organisational boundaries such as those impacted by sustainability and the UK Net Carbon Zero by 2050 target.;	B1: Work collaboratively enabling empowerment and delegation.;B2: Take personal accountability aligned to clear values.;B3: Curious and innovative - exploring areas of ambiguity and complexity and finding creative solutions.;B4: Value difference and champion diversity.;B5: Seek continuous professional development opportunities for self and wider team.;
4	A1234567	11/13/2023	4.5	K1: How to shape organisational mission, culture and values.;	S1: Use horizon scanning and conceptualisation to deliver high performance strategies focusing on growth/sustainable outcomes.;	B4: Value difference and champion diversity.;B5: Seek continuous professional development opportunities for self and wider team.;
Form1

Off-the-job submissions log (2).xlsx
	A	B
6	KSB	Times covered
7	K1: How to shape organisational mission, culture and values.	-
8	K2: Organisation structures; business modelling; diversity; global and horizon scanning perspectives; governance and accountability; technological and policy implications.	-
9	K3: New market strategies, changing customer demands and trend analysis.	-
10	K4: Innovation; the impact of disruptive technologies (mechanisms that challenge traditional business methods and practices); drivers of change and new ways of working across infrastructure, processes, people and culture and sustainability.	-
11	K5: Systems thinking, knowledge/data management, research methodologies and programme management.	-
12	S1: Use horizon scanning and conceptualisation to deliver high performance strategies focusing on growth/sustainable outcomes.	-
13	S2: Set strategic direction and gain support for it from key stakeholders.	-
14	S3: Undertake research, and critically analyse and integrate complex information.	-
15	S4: Lead change in their area of responsibility, create an environment for innovation and creativity, establishing the value of ideas and change initiatives and driving continuous improvement.	-
16	S5: Lead and respond in a crisis situation using risk management techniques.	-
17	B1: Work collaboratively enabling empowerment and delegation.	-
18	B2: Take personal accountability aligned to clear values.	-
19	B3: Curious and innovative - exploring areas of ambiguity and complexity and finding creative solutions.	-
20	B4: Value difference and champion diversity.	-
21	B5: Seek continuous professional development opportunities for self and wider team.	-
Form1

Thank you in advance!

 

[Fluff]

How about

=COUNTIFS(D$2:F$5,"*"&A6&"*")

 

[JoshLyman]

Lovely jubbly! Thanks, as always @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.