Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2776323%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776323%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20I%20am%20using%26nbsp%3B%3C!--%20%20%20%20%20StartFragment%20%20%20%20%20%20--%3E%3CSPAN%20class%3D%22%22%3EMicrosoft%C2%AE%20Excel%C2%AE%20for%20Microsoft%20365%20MSO%20(16.0.14326.20384)%2064-bit.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EI%20want%20to%20know%20if%20there%20is%20a%20formula%20that%20will%20count%20how%20many%20check%20boxes%20that%20are%20%22checked%22%20in%20a%20range%20of%20particular%20cells%20and%20return%20the%20sum%20to%20a%20single%20cell.%20If%20there%20is%2C%20how%20would%20I%20write%20the%20formula%3F%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EDoes%20the%20actual%20%22Check%20Boxes%22%20need%20to%20be%20inserted%20in%20the%20cells%20in%20a%20certain%20way%20in%20order%20to%20use%20it%20as%20criteria%3F%20Or%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EAny%20help%20would%20be%20much%20much%20much%20appreciated!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI've%20attached%20a%20screen%20shot%20of%20my%20worksheet.%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20help.%3C%2FP%3E%3CP%3EAngel_777_1%3C%2FP%3E%3CP%3E%3C!--%20%20%20%20%20EndFragment%20%20%20%20%20%20--%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2776323%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776354%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163881%22%20target%3D%22_blank%22%3E%40Angel_777_1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20one%20of%20the%20cells%20with%20a%20ticked%20check%20box.%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20formula%20bar%20and%20select%20the%20character%20you%20see%20there.%3C%2FP%3E%0A%3CP%3ECopy%20it%20(Ctrl%2BC).%3C%2FP%3E%0A%3CP%3ENow%20select%20a%20cell%20where%20you%20want%20a%20formula%20to%20count%20the%20check%20boxes.%3C%2FP%3E%0A%3CP%3EStart%20entering%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(%3CEM%3Erange_to_count%3C%2FEM%3E%2C%20%22%3C%2FP%3E%0A%3CP%3Ewhere%20%3CEM%3Erange_to_count%3C%2FEM%3E%20is%20a%20range%20with%20check%20boxes.%3C%2FP%3E%0A%3CP%3EPress%20Ctrl%2BV%20to%20paste%20the%20character%20you%20copied.%3C%2FP%3E%0A%3CP%3EFinish%20the%20formula%20by%20typing%20%22)%20so%20that%20it%20looks%20like%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(%3CEM%3Erange_to_count%3C%2FEM%3E%2C%20%22%3CEM%3Ec%3C%2FEM%3E%22)%3C%2FP%3E%0A%3CP%3Ewhere%20%3CEM%3Ec%3C%2FEM%3E%20is%20the%20pasted%20character.%20Press%20Tab%20or%20Enter%20to%20confirm%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2776392%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2776392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163881%22%20target%3D%22_blank%22%3E%40Angel_777_1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20check%20boxes%20are%20to%20be%20any%20more%20than%20a%20decorative%20feature%2C%20it%20is%20normal%20to%20link%20them%20to%20a%20cell%20that%20will%20show%20TRUE%2FFALSE%20depending%20upon%20the%20state%20of%20the%20check%20box.%26nbsp%3B%20The%20linked%20cell%20is%20often%20chosen%20to%20be%20the%20one%20that%20is%20hidden%20by%20the%20overlying%20control.%3C%2FP%3E%3CP%3EAssuming%20the%20cells%20form%20a%20named%20range%2C%20then%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20COUNTIFS(linkedCells%2C%20TRUE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewill%20provide%20a%20count%20of%20checked%20boxes.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi. I am using Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.20384) 64-bit.

I want to know if there is a formula that will count how many check boxes that are "checked" in a range of particular cells and return the sum to a single cell. If there is, how would I write the formula? 

Does the actual "Check Boxes" need to be inserted in the cells in a certain way in order to use it as criteria? Or??

Any help would be much much much appreciated!

I've attached a screen shot of my worksheet.

Thank you in advance for your help.

Angel_777_1

2 Replies

@Angel_777_1 

Select one of the cells with a ticked check box.

Click in the formula bar and select the character you see there.

Copy it (Ctrl+C).

Now select a cell where you want a formula to count the check boxes.

Start entering the formula

=COUNTIF(range_to_count, "

where range_to_count is a range with check boxes.

Press Ctrl+V to paste the character you copied.

Finish the formula by typing ") so that it looks like

=COUNTIF(range_to_count, "c")

where c is the pasted character. Press Tab or Enter to confirm the formula.

@Angel_777_1 

If the check boxes are to be any more than a decorative feature, it is normal to link them to a cell that will show TRUE/FALSE depending upon the state of the check box.  The linked cell is often chosen to be the one that is hidden by the overlying control.

Assuming the cells form a named range, then

= COUNTIFS(linkedCells, TRUE)

will provide a count of checked boxes.