I have a sheet summary where I am using the formula below to count the true number of students on the sheet even though they can be listed multiple times per semester. It works like a charm because the email address is the column used because there is no way there could be a typo there. Sometimes editors will type a students name incorrectly and the typo would be counted as a different person.
=COUNT(DISTINCT([Student Email]:[Student Email]))
I'm also counting the number "status" of each students placement whether cleared, pending, or declined. Below is an example of that formula and it works very well for what I need.
=COUNTIF([Credentialing Status]:[Credentialing Status], "✔Cleared")
Here's my problem:
The sheet shows ALL student placement with different semesters tagged for the entries listed. I need to show counts based on a particular semester. Right now the formulas are only showing a grand total for those the columns and I need it to count the Fall 2021 semester. The column is called Semester.
How do I edit the 2 formulas above to only give me numbers for each placement where the semester is tagged for Fall 2021? I really don't have time to build a metrics sheet and at the time we built our dashboard, it was based on the sheet summary with the above formulas. I just need a quick fix to get this report out and I'll work on building a metric sheet later so that I don't have this issue anymore. Any help will be greatly appreciated.
Help Article Resources
Check out the Formula Handbook template!