Countif, but really complicated

[robgoldstein]

I have a class list with the names of students in column B and columns C,D &E are 3 kids from the list in column B they can be matched with.

In Column J I have their group they have been put into. ( values 1-4)
How can I count how many kids who put them as a potential match C10:E50 are in their group?

So their name would have appear in the kid's list and that kid would have to be in their group. So if the kid(B10) is in group 1, we would have to see how many kids in group 1 also have the name in B10 in columns C,D or E

thanks so much for your help.

 

[CA_Punit]

can you post a sample data using xl2bb???

 

[robgoldstein]

can you post a sample data using xl2bb???

What is xl2bb?

 

[CA_Punit]

Is this what you want.

and xl2bb is an addin used to post excel data in this site you can download it (See Right side of the message box)

Book1
	B	C	D	E	F	G	H	I	J	K
1
2	Name								Group	Count
3	A	D	H	F					1	2
4	B	D	C	A					2	0
5	C	H	F	A					3	1
6	D	A	B	C					1	1
7	E	H	E	A					2	1
8	F	B	H	C					3	1
9	G	H	C	D					4	0
10	H	D	E	F					1	1
11
12
13
Sheet2

Cell Formulas
Range	Formula
K3:K10	K3	=SUM(--ISNUMBER(MATCH(TRANSPOSE(C3:E3),IF($J$3:$J$10=J3,$B$3:$B$10),0)))

 

[Eric W]

This is a little different take:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Student	Choice A	Choice B	Choice C		# of requestors in their group			Group		Group 4
2		Al	Naomi	Lily	Rae		0			1		Deb	Cal	Naomi	Ken
3		Babs	Deb	Pam	Deb		0			2		Heidi	Ken	Quince	Al
4		Cal	Guy	Mark	Jen		0			3		Lily	Guy	Ed	Heidi
5		Deb	Cal	Naomi	Ken		2			4		Pam	Fay	Deb	Mark
6		Ed	Talia	Ken	Ollie		1			1		Talia	Mark	Deb	Lily
7		Fay	Ed	Pam	Al		1			2
8		Guy	Sam	Quince	Deb		3			3
9		Heidi	Ken	Quince	Al		1			4
10		Idris	Guy	Babs	Idris		1			1
11		Jen	Al	Idris	Pam		1			2		Group 3
12		Ken	Jen	Guy	Pam		0			3		Cal	Guy	Mark	Jen
13		Lily	Guy	Ed	Heidi		1			4		Guy	Sam	Quince	Deb
14		Mark	Cal	Ollie	Fay		0			1		Ken	Jen	Guy	Pam
15		Naomi	Jen	Fay	Ed		0			2		Ollie	Heidi	Deb	Al
16		Ollie	Heidi	Deb	Al		0			3		Sam	Fay	Idris	Guy
17		Pam	Fay	Deb	Mark		0			4
18		Quince	Ken	Ken	Ed		0			1
19		Rae	Talia	Deb	Lily		0			2
20		Sam	Fay	Idris	Guy		1			3
21		Talia	Mark	Deb	Lily		0			4
22
Sheet2

Cell Formulas
Range	Formula
G2:G21	G2	=SUMPRODUCT(MMULT(--($C$2:$E$21=B2),{1;1;1})*($J$2:$J$21=J2))

Columns L:O and the highlighting is just an attempt to show my work, so to speak. It appears that CA_PUNIT and I had different interpretations of your request, which is why an example is so helpful.

 

[CA_Punit]

I learn from your posts a lot. Just a beginner in excel. @Eric W

 

[robgoldstein]

Hey all,
Here is the data. I only put info in the kids who are in the same group for simplicity. I want the number in the cell K10 to equal 3. But if their name was in other kids friends who are not in group 1 it would not count it.

Class Placement Excel Document.xlsx
	A	B	C	D	E	F	G	H	I	J	K
9	Current Class	STUDENT NAME	Sex	Friends	Friends	Friends	Should Not	Should Not	Pertinent Notes	Assigned Class (1-4)	Friends
10	JK101	Alexander Weizman	m							1	3
11	JK104	Annie Brass	f	Alexander Weizman	Ruby Callaghan*	Ari Tabesh				1	0
12	JK104	Annie Klug*	f							2	0
13	JK101	Arbel Miller	f	Tamar Regev*	Tyler Mitz	Alexander Weizman				1	0
14	JK101	Ari Tabesh	m							3	1
15	JK101	Aria Moynihan	f							4	0
16	JK104	Ariella Bodzin*	f							2	0
17	JK104	Avner Hana*	m							3	0
18	JK104	Brixton Steinfeld*	m	Max Jacobs	Alexander Weizman	Marlee Gorenstein*				1	0
19	JK101	Celeste Posluns	f								0
20	JK104	Daniel Zaulan-Garpenfeldt	m								0
JK Worksheet

 

[robgoldstein]

Is this what you want.

and xl2bb is an addin used to post excel data in this site you can download it (See Right side of the message box)

Book1
	B	C	D	E	F	G	H	I	J	K
1
2	Name								Group	Count
3	A	D	H	F					1	2
4	B	D	C	A					2	0
5	C	H	F	A					3	1
6	D	A	B	C					1	1
7	E	H	E	A					2	1
8	F	B	H	C					3	1
9	G	H	C	D					4	0
10	H	D	E	F					1	1
11
12
13
Sheet2

Cell Formulas
Range	Formula
K3:K10	K3	=SUM(--ISNUMBER(MATCH(TRANSPOSE(C3:E3),IF($J$3:$J$10=J3,$B$3:$B$10),0)))

Thanks so much for this. But what I am seeing in your example is not what I am looking for. For row 3 the count would be 1 and the only row that would be counted is row 6 because they are in group 1 and they have A as a friend.

 

[robgoldstein]

This is a little different take:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Student	Choice A	Choice B	Choice C		# of requestors in their group			Group		Group 4
2		Al	Naomi	Lily	Rae		0			1		Deb	Cal	Naomi	Ken
3		Babs	Deb	Pam	Deb		0			2		Heidi	Ken	Quince	Al
4		Cal	Guy	Mark	Jen		0			3		Lily	Guy	Ed	Heidi
5		Deb	Cal	Naomi	Ken		2			4		Pam	Fay	Deb	Mark
6		Ed	Talia	Ken	Ollie		1			1		Talia	Mark	Deb	Lily
7		Fay	Ed	Pam	Al		1			2
8		Guy	Sam	Quince	Deb		3			3
9		Heidi	Ken	Quince	Al		1			4
10		Idris	Guy	Babs	Idris		1			1
11		Jen	Al	Idris	Pam		1			2		Group 3
12		Ken	Jen	Guy	Pam		0			3		Cal	Guy	Mark	Jen
13		Lily	Guy	Ed	Heidi		1			4		Guy	Sam	Quince	Deb
14		Mark	Cal	Ollie	Fay		0			1		Ken	Jen	Guy	Pam
15		Naomi	Jen	Fay	Ed		0			2		Ollie	Heidi	Deb	Al
16		Ollie	Heidi	Deb	Al		0			3		Sam	Fay	Idris	Guy
17		Pam	Fay	Deb	Mark		0			4
18		Quince	Ken	Ken	Ed		0			1
19		Rae	Talia	Deb	Lily		0			2
20		Sam	Fay	Idris	Guy		1			3
21		Talia	Mark	Deb	Lily		0			4
22
Sheet2

Cell Formulas
Range	Formula
G2:G21	G2	=SUMPRODUCT(MMULT(--($C$2:$E$21=B2),{1;1;1})*($J$2:$J$21=J2))

Columns L:O and the highlighting is just an attempt to show my work, so to speak. It appears that CA_PUNIT and I had different interpretations of your request, which is why an example is so helpful.

Yes, this is what I am looking for. Thank you so much.

 

[Eric W]

Glad we could help!