please help getting this excel formula right [counting checked boxes]

%3CLINGO-SUB%20id%3D%22lingo-sub-1442509%22%20slang%3D%22en-US%22%3Eplease%20help%20getting%20this%20excel%20formula%20right%20%5Bcounting%20checked%20boxes%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20could%20someone%20help%20me%20out%20here%3A%3C%2FP%3E%3CP%3EI%20created%20a%20list%20in%20excel%20with%20check%20boxes%20and%20would%20like%20to%20count%20only%20the%20checked%20boxes.%20Now%20here's%20the%20problem%20with%20the%20boxes%20as%20they%20are%20shown%20as%20%22true%22%20for%20a%20checked%20box%20and%20false%20for%20an%20empty%20box%2C%20but%20the%20formula%20%3DCOUNTIF(A2%3AA5%2C%22true%22)%20won't%20count%20the%20checked%20ones.%20It%20displays%20%220%22%20even%20though%20some%20boxes%20are%20checked.%20It's%20a%20small%20detail%20but%20would%20make%20things%20so%20much%20easier.%20Thank%20you%20for%20any%20hints%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1442509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442525%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%20getting%20this%20excel%20formula%20right%20%5Bcounting%20checked%20boxes%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690020%22%20target%3D%22_blank%22%3E%40Lenabrandecker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMake%20sure%20you%20are%20counting%20the%20True%20instances%20for%20the%20cells%20which%20are%20linked%20to%20the%20CheckBoxes%20and%20as%20per%20your%20formula%20A2%3AA5%20should%20be%20linked%20to%20the%20four%20CheckBoxes.%20Check%20linked%20cell%20property%20for%20each%20CheckBox%20and%20see%20if%20they%20are%20linked%20to%20the%20correct%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442556%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%20getting%20this%20excel%20formula%20right%20%5Bcounting%20checked%20boxes%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442556%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690020%22%20target%3D%22_blank%22%3E%40Lenabrandecker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20you%20formatted%20the%20checkbox%20to%20determine%20which%20cells%20the%20values%20will%20be%20linked%20to%20as%20highlighted%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wumolad_0-1591353741624.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196692i1465F7E710518037%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22wumolad_0-1591353741624.png%22%20alt%3D%22wumolad_0-1591353741624.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20case%2C%20the%20values%20based%20on%20checkbox%20selection%20are%20reported%20in%20cells%20M1%20to%20M6%2C%20hence%2C%20the%20formula%20I%20used%20was%20based%20on%20the%20TRUE%2FFALSE%20reported%20based%20on%20checking%20or%20unchecking%20the%20boxes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20used%20was%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(M1%3AM6%2CTRUE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20the%20worksheet%20for%20guidance%20and%20do%20let%20me%20know%20if%20any%20clarification%20is%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20in%20your%20own%20case%2C%20if%20you%20still%20want%20to%20use%20A2%20to%20A5%20in%20your%20formula%2C%20ensure%20the%20checkboxes%20are%20linked%20to%20the%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmmanuel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442660%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%20getting%20this%20excel%20formula%20right%20%5Bcounting%20checked%20boxes%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3Ethanks%20a%20lot.%20I%20could%20solve%20it%20by%20changing%20the%20language%20of%20parts%20of%20the%20formula%20(see%20above).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442658%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%20getting%20this%20excel%20formula%20right%20%5Bcounting%20checked%20boxes%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3EThanks%20a%20lot%20for%20your%20detailed%20response.%20I%20ran%20over%20it%20and%20noticed%20the%20problem%20was%20language%20based.%20The%20formula%20of%20the%20box%20when%20checked%20was%20displayed%20in%20german%20language%20%22%20WAHR%22%2C%20as%20is%20the%20formula's%20beginning%20%22Z%C3%84HLENWENN%22%20but%20it%20only%20works%20if%20I%20fill%20in%20the%20english%20property%20%22TRUE%22%20instead%20of%20the%20german%20word.%20So%20eventually%20%5B%20%3CSPAN%20class%3D%22formula-content%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EZ%C3%84HLENWENN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EB2%3AB26%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22TRUE%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%5D%3C%2FSPAN%3E%3C%2FSPAN%3E%20works.%20Also%20I%20should%20mention%20this%20happened%20in%20google%20drive%2C%20while%20editing%20a%20sheet%20online.%20Thanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442732%22%20slang%3D%22en-US%22%3ERe%3A%20please%20help%20getting%20this%20excel%20formula%20right%20%5Bcounting%20checked%20boxes%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690020%22%20target%3D%22_blank%22%3E%40Lenabrandecker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20problem!%20Glad%20your%20issue%20has%20been%20resolved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there, could someone help me out here:

I created a list in excel with check boxes and would like to count only the checked boxes. Now here's the problem with the boxes as they are shown as "true" for a checked box and false for an empty box, but the formula =COUNTIF(A2:A5,"true") won't count the checked ones. It displays "0" even though some boxes are checked. It's a small detail but would make things so much easier. Thank you for any hints

Kind regards

5 Replies
Highlighted

@Lenabrandecker 

Make sure you are counting the True instances for the cells which are linked to the CheckBoxes and as per your formula A2:A5 should be linked to the four CheckBoxes. Check linked cell property for each CheckBox and see if they are linked to the correct cells.

Highlighted

Hi @Lenabrandecker 

 

I believe you formatted the checkbox to determine which cells the values will be linked to as highlighted below:

 

wumolad_0-1591353741624.png

 

In my case, the values based on checkbox selection are reported in cells M1 to M6, hence, the formula I used was based on the TRUE/FALSE reported based on checking or unchecking the boxes.

 

The formula used was 

 

 

=COUNTIF(M1:M6,TRUE)

 

 

See attached the worksheet for guidance and do let me know if any clarification is required.

 

Note that in your own case, if you still want to use A2 to A5 in your formula, ensure the checkboxes are linked to the cells.

 

Cheers

 

Emmanuel

Highlighted

@wumoladThanks a lot for your detailed response. I ran over it and noticed the problem was language based. The formula of the box when checked was displayed in german language " WAHR", as is the formula's beginning "ZÄHLENWENN" but it only works if I fill in the english property "TRUE" instead of the german word. So eventually [ =ZÄHLENWENN(B2:B26;"TRUE"] works. Also I should mention this happened in google drive, while editing a sheet online. Thanks again!

Highlighted

@Subodh_Tiwari_sktneerthanks a lot. I could solve it by changing the language of parts of the formula (see above).

Highlighted

@Lenabrandecker 

No problem! Glad your issue has been resolved.