Formula Help
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.
Best 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 :)
Answers
-
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 :)
-
@Christina Lam OMG....I keep forgetting about the COUNTIFS. Yes, that worked and made my life much easier. Thank you so much!🙏
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!