SUM of COUNTIFS totals in Checkbox Column

Jen V

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!

Leibel S

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

@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

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

Jen V

@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

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