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.

 

[Eric W]

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

Just a beginner? I've been impressed with your posts so far. Probably better than when I started. But I've learned a lot here, and there's still so much more to learn!

 

[robgoldstein]

@Eric W
I thought it was working, but it is not. It is counting the number of kids who requested that person, but it should be counting the number kids they requested that are in their group.
If you scroll down to the row 64 you can see that Alex requested Ari and Annie who are in his group, butit shows he has zero friends.

 

[Eric W]

None of the examples in this thread have a row 64. In looking at your other thread, there are some that go that far, but no row 64 seems to relate to this question. However, given your latest explanation, try:

Book1
	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	0
11	JK104	Annie Brass	f	Alexander Weizman	Ruby Callaghan*	Ari Tabesh				1	1
12	JK104	Annie Klug*	f							2	0
13	JK101	Arbel Miller	f	Tamar Regev*	Tyler Mitz	Alexander Weizman				1	1
14	JK101	Ari Tabesh	m							3	0
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	1
19	JK101	Celeste Posluns	f								0
20	JK104	Daniel Zaulan-Garpenfeldt	m								0
Sheet3

Cell Formulas
Range	Formula
K10:K20	K10	=SUMPRODUCT(COUNTIFS($B$10:$B$20,D10:F10,$J$10:$J$20,J10))

 

[robgoldstein]

None of the examples in this thread have a row 64. In looking at your other thread, there are some that go that far, but no row 64 seems to relate to this question. However, given your latest explanation, try:

Book1
	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	0
11	JK104	Annie Brass	f	Alexander Weizman	Ruby Callaghan*	Ari Tabesh				1	1
12	JK104	Annie Klug*	f							2	0
13	JK101	Arbel Miller	f	Tamar Regev*	Tyler Mitz	Alexander Weizman				1	1
14	JK101	Ari Tabesh	m							3	0
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	1
19	JK101	Celeste Posluns	f								0
20	JK104	Daniel Zaulan-Garpenfeldt	m								0
Sheet3

Cell Formulas
Range	Formula
K10:K20	K10	=SUMPRODUCT(COUNTIFS($B$10:$B$20,D10:F10,$J$10:$J$20,J10))

That did it. Thanks.