Formula Help



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.

Best Answer

  • Christina09
    Christina09 ✭✭✭✭✭✭
    edited 09/30/21 Answer ✓

    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 :)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!