CONCAT cell reference

[colinheslop1984]

Cells A-H in blue refer to workbook 'Dan Participants'.

Columns A and B are linked to an external data source. I have used the CONCAT function to merge the names together from cell A and B.

Cell H references the internal worksheet 'Vacancies', cells C and K coloured red.

The formula works as I want it to if I was to write the name, rather than using CONCAT. However there is a reason why I need to use CONCAT.

I need column H to count how many jobs have been sent (red C) to each individual using column K as a means of counting.

CONCAT was my attempted solution, but have found this doesn't work and need another solution that doesn't require changing the layout of data sources.

Hope this makes sense. I am using excel 2016. I am an intermediate user, anything involving VBA will go over my head.

	A​	B​	C​	H​	C​	K​
	First Name	Surname	CONCAT function	Jobs Sent	Job Vacancy	Candidate
7	Dave	Young	Dave Young	=COUNTIFS(Vacancies!K:K,C7,Vacancies!I:I,">="&'Dan Participants'!B$2,Vacancies!I:I,"<="&'Dan Participants'!B$3)	Retail Assistant	Dave Young Stacey Lloyd
8	Marc	Smith	Marc Smith		Engineering Apprentice	Marc Smith Jacob Andrews
9	Stacey	Lloyd	Stacey Lloyd		Painter & Decorator	Marc Smith Jacob Andrews
10	Jacob	Andrews	Jacob Andrews		Admin Assistant	Stacey Lloyd

 

[Fluff]

I am using excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=COUNTIFS(Vacancies!K:K,"*"&C7&"*",Vacancies!I:I,">="&B$2,Vacancies!I:I,"<="&B$3)