SUM of COUNTIFS totals in Checkbox Column

Options

Hello! I am hoping someone can help me. I am building a sheet that has several layers of parent and children sets using many different column properties to meet a specific client need. I have to use checkboxes to signify the type of business and then need to show total numbers by region, by country, and then by category. I was able to use the =COUNTIFS(CHILDREN(), 1) + "" formula to total the checkboxes under the region parent row but now that I am trying to total the regional rows to show a SUM in the country (and then again for category), I am running into issues.

When using a simple =SUM(CHILDREN()) formula, nothing happens and the empty checkbox just remains. When I tried manually totaling each cell, I have gotten errors as well as an odd situation where instead of i.e. 1 + 2 + 3 totaling 6, it produced 123. Any help would be greatly appreciated!



Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    You formula:

    =COUNTIFS(CHILDREN(), 1) + ""

    Is making the values a text not a number.

    Can you just switch it to:

    =COUNTIFS(CHILDREN(), 1)

  • Jen V
    Jen V ✭✭
    Options

    @Leibel S unfortunately that does not appear to work with checkbox fields (get BOOLEAN EXPECTED error). I had originally been trying that but through the Smartsheet community, the only solution I could find to create those tallies in the subcategories was that. I thought that may be why I was then having issues with SUMs but not sure how to solve.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Do you really have to use checkboxes? Can you use a dropdown with an X or a symbol? That will fix your Boolean issue and let you count.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jen V
    Jen V ✭✭
    Options

    @Mark Cronk in a perfect world, I am hoping to find a solution for the Checkbox per the client's request but did run a test with the X and it solved for my functional need. Makes sense that the COUNTIF formula was turning the results into text vs a number and even when working with numbers only, the SUM formula would not override the checkbox so appears to be my best immediate option. Thank you!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Jen V ,

    Sometime clients have to flex. Glad you have a solution for now. Please accept an answer to file out the discussion. Happy to help anytime.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.