COUNTIF problem with table range

[JoshLyman]

Trying to get column "Times covered" to count how many times a particular 'Apprenticeship KSB' is logged in any of the 'KSB(s) covered' columns in the table on the OTJ log sheet.. but it doesn't seem to be calculating for reasons unknown to me. As you'll see from the second extract below , I've added K1, K3 and K4 to my log, but the tracker is not picking them up/counting them. I'm probably being dumb, but would very much appreciate some help

DRAFT - OTJ Log - v0.1.xlsm
	B	C	D
7	Apprenticeship KSB	Times covered	Notes
8	K1: How to shape organisational mission, culture and values	0
9	K2: Organisation structures; business modelling; diversity; global and horizon scanning perspectives; governance and accountability; technological and policy implications	0
10	K3: New market strategies, changing customer demands and trend analysis.	0
11	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	0
12	K5: Systems thinking, knowledge/data management, research methodologies and programme management	0
13	K6: Ethics and values-based leadership theories and principles	0
4 - KSB TRACKER

Cell Formulas
Range	Formula
C8:C13	C8	=COUNTIF(OTJLog[[KSB(s) covered]:[KSB(s) covered8]],[@[Apprenticeship KSB]])

DRAFT - OTJ Log - v0.1.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
9	Activity date dd/mm/yyyy	OTJ Hours	Non-OTJ Hours	Short description of the activity e.g., assignments, online learning, practical training, lectures	KSB(s) covered	KSB(s) covered2	KSB(s) covered3	KSB(s) covered4	KSB(s) covered5	KSB(s) covered6	KSB(s) covered7	KSB(s) covered8
10	16/08/2023	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.	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.
3 - OTJ LOG

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C10	Cell Value	>7	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F10:M10	List	=Lookups!$D$2:$D$47
B10	Date	between '1 - APPRENTICE INFO'!K18 and 31/12/9999
C10:D10	Custom	=ISNUMBER(C10)

 

[RoryA]

They don't appear to match. There are full stops at the end of each one in the source table but not in the formula table.

 

[JoshLyman]

how odd! You're totally right. I've removed full stops from everything and it now counts as expected. Thanks @RoryA, lifesaver.