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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!