How to use SUMPRODUCT and COUNT question for unique associations

< Back to Search results

 Category: [Excel]  Demo Available 

How to use SUMPRODUCT and COUNT question for unique associations




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?




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



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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use SumProduct array formula
How to use SUMIF
How to combine LARGE and SUMIF - Array formula
How to use SUMPRODUCT 
How to work with COUNTIF with multiple criteria
How to use SUMIF using Variable Columns


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)