CountIf for check boxes

Genevieve P. · 09/24/21

It sounds like you may be doing a cross-sheet formula, looking at a different sheet, is that correct?

If so, your formula should look something like this:

=COUNTIF({Column with Checkboxes}, @cell = 1)

If you're still receiving an error, can you post the formula you're using and a screen capture of the sheet you're looking at (but please block out sensitive data!)

Cheers,

Genevieve

Cindi Meche · 10/06/21

Hi, I am having a similar issue using COUTIF to calculate a column with checkboxes. I am using:

=COUNTIF({Linked FIle Range 1} @cell = Social Media)

It returns #UNPARSABLE

Using quotes around "Social Media" does not solve the issue. Does anyone have a suggestion?

Genevieve P. · 10/06/21

Hi @Cindi Meche

You will need the quotes around "Social Media", and you will also need a comma between the {range} and the criteria!

Try this:

=COUNTIF({Linked FIle Range 1}, @cell = "Social Media")

You could also go right into the criteria, like so:

=COUNTIF({Linked FIle Range 1}, "Social Media")

Cheers,

Genevieve

Cindi Meche · 10/06/21

Thanks, Genevieve!

I went with this version: =COUNTIF({Linked FIle Range 1}, "Social Media")

I'm one step closer, but now it tallies 0 when I know I have 4. I double-checked to make sure I had the correct column of data selected. Any idea why it's not pulling any data?

Do you have any other suggestions?

Screen Shot 2021-10-06 at 2.53.26 PM.png

Screen Shot 2021-10-06 at 2.56.10 PM.png

Cindi Meche · 10/06/21

OK... so dropdowns are not checkboxes. Ha ha ha!

I got it to work using =COUNTIF({Linked File Data Range 1}, CONTAINS("Social Media", @cell))

Genevieve P. · 10/08/21

Hi @Cindi Meche

Thank you for the screen captures, that's very helpful!

I can see that it's not just a dropdown list but a multi-select dropdown which will change the formula you'll want to use. Instead of CONTAINS, try using HAS. HAS is specifically made to see if a multi-select cell has a specific value, amongst other values.

Try this:

=COUNTIF({Linked File Data Range 1}, HAS(@cell, "Social Media"))

We can even take it a step further since you have "Social Media" written in your formula sheet:

=COUNTIF({Linked File Data Range 1}, HAS(@cell, Criteria@row))

Then you can drag-fill this formula down without needing to change what's "in quotes" each row.

Let me know if this works for you!

Cheers,

Genevieve

Sashank Gutala · 02/02/22

Thank you Andrée Starå, that helps a lot

Andrée Starå · 02/03/22

@Sashank Gutala

Excellent!

Glad to hear it was helpful!

Yanneth · 03/14/22

I am having problems counting the total of checkboxes marked in the sheet.

I want to capture the total of column FY-21 with checkboxes marked. Any help is appreciated.

Andrée Starå · 03/14/22

Hi @Yanneth

Adding the answer here as well.

I hope you're well and safe!

Try something like this.

If it's placed in another column, than the range.

=COUNTIFS([CHECKBOX FY-21]:[CHECKBOX FY-21], 1)

If it's placed in the same column as the range.

=COUNTIFS([CHECKBOX FY-21]:[CHECKBOX FY-21], 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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ly-Xu Ngo · 03/22/22

HI,

I would like if you can help me with this formula please.

=COUNTIF({Cadre21_registre des inscriptions}, CONTAINS(@cell, "1- Autobus"), CONTAINS(@cell, "101A Communication entraîneur") = 1)

I would like to count 2 critera : 1- Autobus (Colum "Unité Sectorielle") + check box (colum "101A Communication entraineur") this sheet is on "Cadre21 registre des inscriptions"

Thank you :)

Dieter21 · 03/23/22

No matter what I try, I am still getting #UNPARSABLE -- Also, I don't want to count the whole column. I ony wants to count rows 3-13 for this specific formula. It should be returning 0 since none are checked, but I'm just getting the error. Any help?

Screen Shot 2022-03-22 at 11.28.52 PM.png

Genevieve P. · 03/23/22

https://community.smartsheet.com/discussion/comment/323550#Comment_323550

Hi @Ly-Xu Ngo

If you're looking for 2 criteria, you'll want to use a plural COUNTIFS with an S at the end.

You also don't need to use CONTAINS if you're looking for one specific value (contains is if you're looking for partial text within a cell, not a full match).

Try this:

=COUNTIFS({Cadre21_registre des inscriptions}, "1- Autobus", {101A Communication entraîneur}, 1)

Note that you will need to create a second {cross sheet reference} to find the column with your checkboxes.

Cheers,

Genevieve

Genevieve P. · 03/23/22

https://community.smartsheet.com/discussion/comment/323614#Comment_323614

Hi @Dieter21

It looks like the column name you've typed [in these] is incorrect. You've typed [Checkbox Complete] but the column to the left is only titled "Complete"

If you only want to count a specific selection of rows, you can add these row numbers after each column reference, like so:

Complete3:Complete13

Try this:

=COUNTIFS(Complete3:Complete13, 1)

See: Create a Cell or Column Reference in a Formula

Cheers,

Genevieve

CountIf for check boxes

Answers

Help Article Resources

Categories