COUNTIFS(OR - Count the number of rows with a checkbox in column A, B, or C

Options
Kayla
Kayla ✭✭✭✭✭

Trying to count how many rows have a checkbox checked in column A, B, OR C. The result should be 4 (Row 1, 3, 4, and 6), but I can only get the formula to work if all 3 checkboxes (A, B, and C) are checked.


Answers

  • Amanda Alv
    Amanda Alv ✭✭✭✭✭
    edited 03/26/22
    Options

    Hi there,

    There may be a more direct way, but as COUNTIF counts cells and not rows, I think we need a helper column.

    I would suggest setting up a Helper column (I called it "Checkbox") to identify rows with at least one checkmark. From there, use Sheet Summary to count the total number of rows that identified as having a checkmark.

    Formula for the support column: =IF(OR(ColumnA@row = 1, ColumnB@row = 1, ColumnC@row = 1), "Checked", "Not Checked")

    Formula for the Sheet summary: =COUNTIF(Checkbox:Checkbox, "Checked")

    That should get you the correct result- hope the method will work for you!

  • Kayla
    Kayla ✭✭✭✭✭
    Options

    @Amanda Alv, I should have included in the original post that I do not want to add a helper column to the source sheet. I know I can go this route but I’m certain there is a way to do it. COUNTIFS will count rows, if A is checked, B is checked and C is checked, so I’m assuming there should be a way to count rows if one or more of those situations occurs.

    Hopeful someone will chime in with the right formula. It seems like it’s at my fingertips and I am stuck.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey Kayla

    @Amanda Alv is correct - when you are trying to first capture filtered data across a row, then summarize it within the sheet, you need to individually capture the row data first with a helper column. Whether it's Amanda's formula or a different approach, one needs to first evaluate if the row has any of the boxes checked - which , in your case, you want marked as One Count regardless of how many checked cells you see in that row. A helper column must do this for you. Then the summarization of all the rows needs to happen - which is the total count you are looking for.


    Kelly