Count Distinct Multiple Criteria

[pickslides]

Hi

I have attached a file. I wish to do a distinct count of Student ID by Year Level and Subject.

Thanks, Q

[Estevaoba]

Array enter in G2 (paste, then confirm with Ctrl+Shift+Enter, instead of just Enter), then copy across and down:

Please Login or Register  to view this content.

Good luck!

[bebo021999]

Alternative:

Please Login or Register  to view this content.

Ctrl-Shift-Enter

[alansidman]

an alternative way is with Power Query

Excel 2016 (Windows) 32 bit

	G	H	I	J
8	Subject	7	8	9
9	Health	2	12	4
10	Humanities	6	4	2
11	Languages	4	12	2
12	Literacy	3	8	4
13	Math	7	8	4

Sheet: Sheet1

Mcode:

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Glenn Kennedy]

or an ordinary formula (just ENTER:

=SUM(INDEX(($C$2:$C$100=G$1)*($B$2:$B$100=$F2)*($A$2:$A$100<>"")/COUNTIFS($C$2:$C$100,$C$2:$C$100&"",$B$2:$B$100,$B$2:$B$100&"",$A$2:$A$100,$A$2:$A$100&""),0))

[AliGW]

@pickslides

Is your forum profile up-to-date? If you have moved on from Excel 2010, then it will need updating, please.