# COUNTIFS on checkbox columns with multiple criteria

Options
✭✭✭✭

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)

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

Options

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

• ✭✭✭✭
Options

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

• Options

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?

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Thanks Andree,

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

I hope that makes sense

• Options

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

• Options

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

• Options

as soon as I move everything disappears

Options

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

• Options

okay, now I feel really stupid!

But thank you very much

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!