**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.