COUNTIFS(OR with multiple checkboxes

erin.smith

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"))

Paul Newcome

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

Leibel S

@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

@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)