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.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!