In cell code ::: Count # of times a name occurs in range, then count next column

[IGPOD]

This sounds confusing, but I have a range named "Names2" I want to count how many time a specific name populates inside that range and then count the next column.

I've provided picture to help. I only need the solution for cell L4... I've tried and tried but continue to fail. Thanks in advance for the assistance!!

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3												Yes	No	Maybe	Unknown	Tentative
4			Name	Answer	Name	Answer	Name	Answer			Bob	2	0	0	1	0
5			Bob	Yes	Earl	No	Lisa	Yes			Lisa	1	1	0	0	0
6			Lisa	No	Samantha	Yes	Bob	Yes			Greg	0	0	1	0	0
7			Greg	Maybe	Bob	Unknown	June	Yes			Susan	1	0	0	1	0
8			Susan	Unknown	Eric	Yes	Samantha	Tentative			Samantha	1	0	0	0	2
9			Samantha	Tentative	June	Yes	Susan	Yes			Earl	0	1	0	0	0
10											Eric	1	0	0	0	0
11											June	2	0	0	0	0
12
Sheet1

Cell Formulas
Range	Formula
L4:P11	L4	=COUNTIFS(Names2,$K4,OFFSET(Names2,,1),L$3)

Named Ranges
Name	Refers To	Cells
Names2	=Sheet1!$C$4:$H$9	L4:P11

If you have the dynamic array functions, you can do this in a single formula. It would help if you updated your profile to show the version of Excel you're using.

 

[Fluff]

What version of Excel are you using?

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’)

If you have 365 with the latest functions, another option is

=COUNTIFS(DROP(Names2,,-1),K4:K11,DROP(Names2,,1),L3:P3)