OzGrid

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

Obtained from the OzGrid Help Forum.

Solution provided by Carim.