Count one site pending circuit

RobNY2

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

Genevieve P.

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