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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!