Formula Help

jgneely72151

Hello!

2 things...

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.

Christina09

Hi there,

You can try to use countifs instead of countif.

countifs let you have multiple critera.

=COUNTIFS([Credentialing Status]:[Credentialing Status], "✔Cleared", [semester]:[semester], "Fall 2021 semester")

Hope this helps :)

Christina09

Hi there,

You can try to use countifs instead of countif.

countifs let you have multiple critera.

=COUNTIFS([Credentialing Status]:[Credentialing Status], "✔Cleared", [semester]:[semester], "Fall 2021 semester")

Hope this helps :)

jgneely72151

@Christina Lam OMG....I keep forgetting about the COUNTIFS. Yes, that worked and made my life much easier. Thank you so much!🙏