Excel: counting the "either or" occurrence of multiple criteria in column B while not double-counting duplicates in column A

Question

Here's some sample excel data that I spun up to describe the problem that I'm facing.

StudentID	Subject	ClassName
001	Math	Algebra I
002	Science	Biology
002	Math	Algebra II
002	History	US History
003	Math	Geometry
003	Science	Chemistry
004	English	Playwriting
004	Math	Trigonometry
004	Language	Spanish I
004	Language	Italian I
005	English	Playwriting
005	English	Speech Writing
006	Language	Linguistics
006	Science	Physics
006	English	Rhetoric
007	Language	Spanish II
007	Math	Pre-Calculus
008	History	World History
008	Language	French I
008	English	Mythology
009	English	Poetry

• Every column uses the string data type

I'm struggling to figure out an excel function/formula that will count how many distinct students are taking either a Math or Science course but making sure to only count each student once. So for the above example data, there are 6 distinct students who took either a Math or Science course.

Most of the information I've found online has solutions that are related to but not exactly what I want. This problem is simpler in a language like Python (where you can filter by subject and then de-duplicate the StudentID) but I'm having a hard time since I'm not super familiar with the nitty-gritty of excel functions.

I've tried playing around with various excel functions like countifs and sumproduct but haven't made much progress and nothing has worked so far.

Any help would be greatly appreciated! Please let me know if any clarifications would be helpful.

Answer 1

Try using the following formula:

---

• Formula used in cell E2

=ROWS(UNIQUE(FILTER(A2:A22,ISNUMBER(XMATCH(B2:B22,{"Math","Science"})))))

---

Another alternative using MMULT( )

---

• Formula used in cell E2

=ROWS(UNIQUE(FILTER(A2:A22,MMULT(N(B2:B22={"Math","Science"}),{1;1}))))

---