Count unique records with specific row values?

[charlesfarrell]

I have a spread sheet of students and each program they are enrolled into.

I want to count the number of student that have certain values in the row.

For example, how many 'New Students' study at Enrollment Campus 'SYD' and school 'COU'

Any help you could offer would be greatly appreciate, I am new to EXCEL and still learning nested formulas.

Thanks,
Charlie

Student No	Year	Term	Enrolment Campus	School	Payment Type	Mode	Enrol Category	Award Mod Attend	Module	State of Residence	Enrolment Report
162844	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1091	QLD	New Student
162844	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1101	QLD	New Student
162844	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC2111	QLD	New Student
171220	2017	TERM2	MEL	COU	FEEHELP	OL	ONLINE	MM	BESC1061	NSW	Re-enrolling Student
171220	2017	TERM2	MEL	COU	FEEHELP	OL	ONLINE	MM	BESC1091	NSW	Re-enrolling Student
174339	2017	TERM2	MEL	COU	FEEHELP	OC	ONCAMPUS	MM	BESC2931	VIC	Re-enrolling Student
175176	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1001	VIC	New Student
175176	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1051	VIC	New Student
175176	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1101	VIC	New Student
185017	2017	TERM2	MEL	COU	UPFRONT	OC	ONCAMPUS	OC	BESC2931	VIC	New Student
202136	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1091	QLD	Re-enrolling Student
213154	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1091	NSW	Re-enrolling Student
213154	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1911	NSW	Re-enrolling Student
215187	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1001	VIC	New Student
215187	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1101	VIC	New Student
216681	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1071	VIC	Re-enrolling Student
220208	2017	TERM2	MEL	COU	FEEHELP	OL	ONLINE	FD	BESC1071	VIC	New Student
220208	2017	TERM2	MEL	COU	FEEHELP	OL	ONLINE	FD	BESC1091	VIC	New Student
222140	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1051	VIC	Re-enrolling Student
222140	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1071	VIC	Re-enrolling Student
222140	2017	TERM2	SYD	COU	FEEHELP	OL	ONLINE	FD	BESC1091	VIC	Re-enrolling Student

<tbody>
</tbody>

 

[SyedUsman]

=COUNTIFS($L$2:$L$22,"New Student",$E$2:$E$22,"COU",$D$2:$D$22,"SYD")

 

[Aladin Akyurek]

@charlesfarrell

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($D$2:$D$22="SYD",IF($E$2:$E$22="COU",IF($L$2:$L$22="new student",$A$2:$A$22))),$A$2:$A$22),1))

 

[charlesfarrell]

So both these formula work, what is the difference between them, in the sense that, is it more advisable to use one over another of is it just personal preferance?

SyedUsman's formula was easier for me to understand and use in other cells.

 

[charlesfarrell]

Thanks for this, was exactly what I was looking for, I was making it over complicated.

 

[Aladin Akyurek]

Thanks for this, was exactly what I was looking for, I was making it over complicated.

Thanks for what? The suggestion I made executes a conditional unique count, i.e., it won't count a student twice when SYD, COU, and new student. Is this not what you require?

 

[charlesfarrell]

Thanks for what? The suggestion I made executes a conditional unique count, i.e., it won't count a student twice when SYD, COU, and new student. Is this not what you require?

Hi Aladin, thank you so much. Your formula is exactly what I was after, the previous formula was showing total records, not unique records. I knew I had to execute a FREQUENCY Function, but couldn't figure it out.


Why Control+shift+enter, not just enter?

 

[Aladin Akyurek]

Hi Aladin, thank you so much. Your formula is exactly what I was after, the previous formula was showing total records, not unique records. I knew I had to execute a FREQUENCY Function, but couldn't figure it out.

You are welcome.

Why Control+shift+enter, not just enter?

The array-processing formulas require control+shift+enter in Excel (Google Sheets, for example, require wrapping such formulas into an ARRAYFORMULA call) in order to signal Excel that "array-processing" is intended. Note that some of the array-processing formulas can be done with just enter when built with array-processing functions like SumProduct, Lookup, etc.

For more on array-formulas, see: http://www.emailoffice.com/excel/arrays-bobumlas.html