Cannot calculate checkbox column
I am trying to calculate the number of checked cells in a column (named 'Done'), however I keep getting errors!
The checked ('Done') column in question is pulling from another sheet using index links so will throw up a tick in the check box and a #NO MATCH accordingly.
The calculation i am using to pull from the checked column is: =COUNTIF(Done:Done, "1"). This will throw up #NO MATCH
Is there a way to ignore the #NO MATCH so the calculation works?
Thanks
Answers

Hi @Dan Harris
You may want to put an IFERROR in your "Done" column formula so that, if there is no match, it shows blank. For example:
=IFERROR(...formula you were using in the Done column..., "")
Let me know if it works!
Best,
Heather

Hi
Not sure I understand, I put =IFERROR(Done:Done, "1") but it just gives a figure of 1 when there are hundreds of entries in that column, can you advise on which formula would work?
Thanks

Are you able to help, am getting pretty desperate to fix this now and having to make manual changes all the time!
Thanks

My apologies. I misread the original post.
The problem is the quotes around the 1. Using the quotes means you are looking for that specific text value. Try removing them and see if that helps.=COUNTIF(Done:Done, 1)I suggest @Heather D's solution of using the IFERROR. You do not want to put it around the COUNTIF though. You want to put it around the formula that is populating the individual checkboxes themselves.

@Paul Newcome Hi Paul,
Sorry but it does not work if I use formula below!
=IFERROR(Done:Done, "1") I get a return of 1 for every cell even when it says #NO MATCH ie its counting #NO MATCH as an entry which I don't want!
Can you help?
Thanks

What is the formula you are using the check each box on each row? THAT is the one you want to wrap in the IFERROR.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!