Counting checkboxes in a column when using column formulas

Options
fkooman89716
fkooman89716 โœญโœญ
edited 12/21/22 in Formulas and Functions

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.

Screenshot 2020-11-09 at 20.12.39.png

If select the boxes in the sheet itself, the count does go as expected.

Screenshot 2020-11-09 at 18.59.23.png


Do you have a solution for this?

Best Answer

Answers

  • fkooman89716
    fkooman89716 โœญโœญ

    Thanks so much for this simple solution! So effective :-)

  • No problem at all! I'm glad it worked for you.

    Need more information? ๐Ÿ‘€ | Help and Learning Center

    ใ“ใ‚“ใซใกใฏ (Konnichiwa), Hallo, Hola, Bonjour, Olรก, Ciao!๐Ÿ‘‹ | Global Discussions

  • Jennifer Loeper
    Jennifer Loeper โœญโœญ

    =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

    Need more information? ๐Ÿ‘€ | Help and Learning Center

    ใ“ใ‚“ใซใกใฏ (Konnichiwa), Hallo, Hola, Bonjour, Olรก, Ciao!๐Ÿ‘‹ | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!