How to use sumproduct and count question for unique associations

How to use SUMPRODUCT and COUNT question for unique associations

Requirement:

The user has student first names in Column A (Mike, John, Sally, etc.). School subjects in Column B (Math, Science, Language, History) which represent categories. The number of times the subject has been taken by "a" student in Column F. The user is trying for a combination of perhaps sumproduct and countif to give me the number of unique students taking math or a science subject. For example, Algebra, Geometry and Calculus are all "Math", but Mike may only take Algebra and John may take Algebra and Geometry. The Math count should be 3 and unique students should be 2. How can a formula be used for summing or counting the 2?

Solution:

In order to get a count of Unique students with the condition of a specific group ... you need to have an Array Formula :

Code:

=SUM(--(FREQUENCY(IF($B$2:$B$7=E2,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW(A2)+1)>0))

Student	Group	Class	Group	Count	Different Students	Array
Mike	Math	Algebra	Math	3	2	2
John	Math	Algebra	History	1	1	1	looking for a formula, but VBA ok too
Sally	History	US History	Language	2	2	2	(right now, written in manually)
John	Math	Geometry
Sally	Language	Spanish					You need to use an Array Formula (CSE)
Mike	Language	French
							use Control Shift Enter … and Not Enter

Group	Count	Different Students	Array
Math	=COUNTIF(B:B,E2)	2	=SUM(--(FREQUENCY(IF($B$2:$B$7=E2,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW(A2)+1)>0))
History	=COUNTIF(B:B,E3)	1	=SUM(--(FREQUENCY(IF($B$2:$B$7=E3,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW(A3)+1)>0))
Language	=COUNTIF(B:B,E4)	2	=SUM(--(FREQUENCY(IF($B$2:$B$7=E4,MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW($A$2:$A$7)-ROW(A4)+1)>0))