Count one site pending circuit
I need a formula to count how many sites still have one circuit to be completed
There are two columns "1st Circuit Status and 2nd Circuit Status"
If one cell has "Circuit Delivered" and another cell has "anything else beside "Circuit delivered or a blank cell" counts one site
If two cells have "Circuit Delivered" they don't count
if both cells from both columns have "N/A", it doesn't count
If both cells from both columns have "canceled" doesn't count
If one cell from one column has either "N/A" or "Cancelled" and another cell from the other column has anything besides "Circuit Delivered" counts one site
if both columns have a "blank" cell, it counts one site
It only counts 1 site if any cell from "1st Circuit Status and 2nd Circuit Status" has anything else besides "Circuit Delivered"
Example
Thank for your help very much! 😀
Rob
Answers
-
Hi @RobNY2
I assume that you're looking for one, overall COUNT, is that correct? What I would do in this instance is create a helper column to evaluate each row individually, creating a count just like your example! Then I'd SUM this helper column together.
Based on your criteria above, I would actually suggest writing an IF statement to just look for your three conditions for when it should return a 0:
If two cells have "Circuit Delivered" they don't count
if both cells from both columns have "N/A", it doesn't count
if both cells from both columns have "Cancelled", it doesn't count
=IF(OR(AND([1st Circuit Status]@row = "Circuit Delivered", [2nd Circuit Status]@row = "Circuit Delivered"), AND([1st Circuit Status]@row = "N/A", [2nd Circuit Status]@row = "N/A"), AND([1st Circuit Status]@row = "Cancelled", [2nd Circuit Status]@row = "Cancelled")), 0, 1)
That way, anything other than those three statements will return 1, and you can SUM the column. Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!