How to count rows if box is checked in separate column?

I have a helper column to identify the columns that require data to be entered. I need help creating a formulat that will do the following:

  1. Count the total number of rows within a specific range of the "Entity Details" column, and have a box checked in the "Data to be Entered Column". (For example Rows 10-25, that have a checked box @row)
  2. Count the total number of blank rows, or rows that read "Please Update" within a specific range of the "Entity Details" column, and have a box checked in the "Data to be Entered Column".


Answers

  • Krissia B.
    Krissia B. Moderator

    Hello @GDMIL ,

    What do you mean with - "Count the total number of rows within a specific range of the "Entity Details" column, and have a box checked in the "Data to be Entered Column". With this question, what would the criteria be for the rows Entity Details? Or is it just count all rows that has checked box? Because if that is, then the formula would like below.

    =COUNTIF([Data to be Entered]:[Data to be Entered], 1)


    For this question - Count the total number of blank rows, or rows that read "Please Update" within a specific range of the "Entity Details" column, and have a box checked in the "Data to be Entered Column". The formula used is below.

    =COUNTIFS([Entity Details]:[Entity Details], OR(HAS(@cell, "Please Update"), HAS(@cell, ="")), [Data to be Entered]:[Data to be Entered], 1)


    See sample screenshot of what it looks like.

    Let me know if i misunderstood your question! I hope this helps.


    Cheers~

    Krissia

  • GDMIL
    GDMIL ✭✭

    Hello @Krissia B !

    The first formula worked great! .....=COUNTIF([Data to be Entered]:[Data to be Entered], 1).

    However, for the 2nd portion of my question, I am only wanting to count the blank rows within the "Entity Details" column that have a checked box within the "Data to be Entered" column.

    Based on your screenshot, the top number would remain 7, but the bottom number should be 1.

    Any help is much appreciated!

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @GDMIL This formula should work:

    =COUNTIFS([Data to be Entered]:[Data to be Entered], 1, [Entity Details]:[Entity Details], ="")

    (there are probably more elegant ways to check for blanks, but I use this approach a lot)

    dm

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!