Cannot calculate checkbox column

Dan Harris

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

Heather Duff

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

Dan Harris

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

Dan Harris

Hi@Heather D

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

Thanks

Paul Newcome

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.

Dan Harris

@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

Paul Newcome

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.