Counting checkboxes in a column when using column formulas
Hello,
Very happy with the column formula functionality, I used it in my sheets, like this:
Check the checkbox in the Catalogues (column), IF Hosted Catalogue OR Punch Out L1 OR Punch Out L2 is checked.
=IF(OR([Hosted Catalogue]@row = 1; [Punch Out L1]@row = 1; [Punch Out L2]@row = 1); 1; 0) --> convert into column formula.
Now I want to calculate how many checkboxes in the catalogue are checked, by using the count children formula, like this =COUNT(CHILDREN(Catalogues32)).
The result is that all lines are counted, not only the checked ones, please see Completed Catalogues Q2 2020.
If select the boxes in the sheet itself, the count does go as expected.
Do you have a solution for this?
Best Answer
-
Try using a COUNTIF instead of a COUNT, where you add in the criteria that the box should be checked (or should be 1):
=COUNTIF(CHILDREN(Catalogues32), 1)
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Try using a COUNTIF instead of a COUNT, where you add in the criteria that the box should be checked (or should be 1):
=COUNTIF(CHILDREN(Catalogues32), 1)
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks so much for this simple solution! So effective :-)
-
No problem at all! I'm glad it worked for you.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
=COUNTIFS({College funding}, <>"Approved", {Division reference}, "FA-Finance and Administration", {fund confirm}, 1)
This is returning a 1 when the checkbox (fund confirm) is not checked. Help!
-
Hey @Jennifer Loeper
I've tested your formula on one of my sheets and it's working as expected. Is it possible that it's counting something else? I would test each value separately to see where the inconsistency lies... ex:
=COUNTIFS({College funding}, <>"Approved")
=COUNTIFS({Division reference}, "FA-Finance and Administration")
=COUNTIFS({fund confirm}, 1)
Then do a Filter for each condition in the source sheet to confirm the calculations. You can also try adding an = sign in front of the 1 to see if that changes anything (but it shouldn't make a difference):
=COUNTIFS({College funding}, <>"Approved", {Division reference}, "FA-Finance and Administration", {fund confirm}, =1)
Let me know if this has worked for you, or if you were able to find the cause of the incorrect number!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!