Formula double-counting
Hello, hoping someone can help me rewrite the formula below:
=COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true, {Expiring-Expired}, true)
Right now it's only counting if all things are true but what I want is for the count to be limited to if the "at risk check" OR "expiring-expired" is true, not both. I tried to just break up the formula into two columns but ran into the next issue. There are several instances where both "at risk" and "expiring" are checked. Can I get it to not double-count those instances?
Thank you to anyone who can help!
Answers
-
I think you would just need to expand the formula to count each value separately, then subtract them together. So something like:
=COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true) +
COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {Expiring-Expired}, true) -
COUNTIFS({Certification Name-Primary}, "NSE2", {Reviewed&Approved}, true, {At risk check}, true, {Expiring-Expired}, true)
So the first two parts will calculate the individual check boxes, then the third part would subtract the instances where they are both checked.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!