COUNTIFS on checkbox columns with multiple criteria

Christy Kooiman · 06/17/21

Is there a way to have a countifs formula on multiple columns? I want to count the cells where PM=Alex and there's a checkbox in the last 4 columns.

=COUNTIFS(PM:PM, "Alex", [Agenda 6/7/21]:[Agenda 6/7/21], 1, [Min 6/7/21]:[Min 6/7/21], 1, [Scorecard 6/7/21]:[Scorecard 6/7/21], 1, [Updates 6/7/21]:[Updates 6/7/21], 1)

Genevieve P. · 06/18/21

Hi @Christy Kooiman

The current formula will only count the rows where all of your criteria are met, so where all 4 of those columns have a checkbox. This means if only 3 of the columns have a checkbox in a row, then this formula will skip that row.

Are you instead looking to count each individual checkbox as a count of 1 (as long as it belongs to "Alex")? If so, you will want to add multiple COUNTIFS statements together, like so:

=COUNTIFS(PM:PM, "Alex", [Agenda 6/7/21]:[Agenda 6/7/21], 1) + COUNTIFS(PM:PM, "Alex", [Min 6/7/21]:[Min 6/7/21], 1) + COUNTIFS(PM:PM, "Alex", [Scorecard 6/7/21]:[Scorecard 6/7/21], 1) + COUNTIFS(PM:PM, "Alex", [Updates 6/7/21]:[Updates 6/7/21], 1)

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Kimberly Loveless · 06/17/21

Is it giving you a particular error with the formula you gave?

Christy Kooiman · 06/17/21

No error, but its not adding up correctly. I should be getting a total of 50 and I'm getting a total of 15

Genevieve P. · 06/18/21

Hi @Christy Kooiman

The current formula will only count the rows where all of your criteria are met, so where all 4 of those columns have a checkbox. This means if only 3 of the columns have a checkbox in a row, then this formula will skip that row.

Are you instead looking to count each individual checkbox as a count of 1 (as long as it belongs to "Alex")? If so, you will want to add multiple COUNTIFS statements together, like so:

=COUNTIFS(PM:PM, "Alex", [Agenda 6/7/21]:[Agenda 6/7/21], 1) + COUNTIFS(PM:PM, "Alex", [Min 6/7/21]:[Min 6/7/21], 1) + COUNTIFS(PM:PM, "Alex", [Scorecard 6/7/21]:[Scorecard 6/7/21], 1) + COUNTIFS(PM:PM, "Alex", [Updates 6/7/21]:[Updates 6/7/21], 1)

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Christy Kooiman · 06/18/21

That worked perfectly! Thank you so much for your help!

Dave Bowie · 08/16/22

I'm using this formula =COUNTIFS([On Leave]@row, 1, [Sick Leave]@row, 1)

on two columns of checkboxes and it isn't working (it is returning zero)

can someone help me?

Andrée Starå · 08/16/22

Hi @Dave Bowie

I hope you're well and safe!

I think this is what you're looking for.

Try something like this.

=COUNTIF([On Leave]:[On Leave], 1) + COUNTIF([Sick Leave]:[Sick Leave], 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dave Bowie · 08/16/22

Thanks Andree,

But I need a total for the row only, not the whole column

I hope that makes sense

Dave Bowie · 08/16/22

I tried this and the correct answers show for a moment then disappear??????

Dave Bowie · 08/16/22

=COUNTIF([On Leave]@row, 1) + COUNTIF([Sick Leave]@row, 1)

Dave Bowie · 08/16/22

as soon as I move everything disappears

Genevieve P. · 08/16/22

Hi @Dave Bowie

In your second image it looks like you may have scrolled over to the left a bit, which will have cut off the end part of that column (note how the grey box isn't complete?)

I would suggest making your column thinner, so that your numbers are closer in. Another option would be to adjust the formatting of that column so the numbers appear on the left side of the cell instead of the right, or in the center.

Cheers!

Genevieve

Dave Bowie · 08/16/22

okay, now I feel really stupid!

But thank you very much

Andrée Starå · 08/16/22

@Dave Bowie

Excellent!

Happy to help!

Sorry about the confusion on row/column.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

COUNTIFS on checkbox columns with multiple criteria

Best Answer

Answers

Help Article Resources

Categories