How to use SUMPRODUCT and COUNT question for unique associations
Category: [Excel] Demo Available
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.
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.