Counting checkboxes in a column when using column formulas

fkooman89716

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?

Genevieve P.

Hi @fkooman89716

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

Genevieve P.

Hi @fkooman89716

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

fkooman89716

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

Genevieve P.

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

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!

Genevieve P.

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