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

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

 

[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