COUNTIFS(OR with multiple checkboxes

Options

Hi,

I have a formula where I am trying to say in column 1 count if checkbox A is checked OR in column 2 if checkbox B is checked.

Here is the entire formula with the portion I cannot figure out in bold:

=COUNTIFS(Simulation:Simulation, "Phillips ED", Complete:Complete, "0", Type:Type, OR(@cell = "Pilot", @cell = "Simulation"), OR(Submitted:Submitted = "1", [Schedule Confirmed]:[Schedule Confirmed] = "1"))

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You'll need to do two separate COUNTIFS (one for each checkbox column) and then add them together.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @erin.smith

    @Paul Newcome suggestion works provided they are never both checked...

    If that is not the case you can make a helper column that evaluates submitted and scheduled confirmed columns to mark that they should be counted.

    Alternatively you can use the below formula, you would need to add an 'auto number' column (named Row ID):

    =COUNTIFS(Simulation:Simulation, "Phillips ED", Complete:Complete, 0, Type:Type, OR(@cell = "Pilot", @cell = "Simulation"), [Row ID]:[Row ID], OR(INDEX(Submitted:Submitted, MATCH(@cell, [Row ID]:[Row ID], 0)) = 1, INDEX([Schedule Confirmed]:[Schedule Confirmed], MATCH(@cell, [Row ID]:[Row ID], 0)) = 1))

    side note your formula was wrapping the numbers in quotation marks, I changed that.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Leibel Shuchat Good catch. To avoid double counting if both are checked, I usually use 3 COUNTIFS. First Box checked + second box checked - both boxes checked.


    =COUNTIFS(ColumnA:ColumnA, @cell = 1) + COUNTIFS(ColumnB:ColumnB, @cell = 1) - COUNTIFS(ColumnA:ColumnA, @cell = 1, ColumnB:ColumnB, @cell = 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!