Count rows where ANY of the criteria are met.

Options
SJ Sellers
SJ Sellers ✭✭✭✭
edited 09/07/22 in Formulas and Functions

I need to count all the rows in my sheet where any one of the three required columns are blank. I want a Sheet Summary formula that stores the result.

Psuedo-code: CountIf Status is blank OR if Owner is blank OR if Department is blank

For reasons I do not want to get into here 😁, I do NOT want to use a helper column for this task.

The only thing I can think to do is count rows where Status is blank PLUS rows where Owner is blank PLUS rows where Department is blank MINUS rows where Status/Owner/Department are blank times 2 MINUS rows where only Status/Owner are blank MINUS rows where only Status/Department are blank MINUS rows where only Owner/Department are blank.

Is there a better way I'm just not thinking of?

Tags:

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 09/07/22
    Options

    The countifs are probably going to be the easiest way. I tried to make a workaround using some text parsing, but it fails one of the 7 possibilities (you can choose which one it fails, but one has to not work)

    =LEN(JOIN(Owner1:COUNT8, ",")) - LEN(SUBSTITUTE(SUBSTITUTE(JOIN(Owner1:COUNT8, ","), ",,false", ".,fals"), ",,", ","))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!