Percentage of ticked boxes completed. Column total.

Georgia Wyllie

I have used Countif formulas to calculate the total completion of ticked boxes for the row of columns but I am having some difficulty finding the total completion of the columns. Can anyone help me establish the correct formula to gain the total of ticked boxes for a row. I am fairly confident that the formula is correct but I am getting a Bootlean expected error due to the formatting of the columns.

Thank you in advance.

Nick Korna

Ah - you're trying to do the sum at the "end" of the column, and getting the boolean error there.

Have you considered using the sheet summary instead?

=COUNTIF([Quarterly WIP Completion]1:[Quarterly WIP Completion]91, 1) / COUNT([Quarterly WIP Completion]1:[Quarterly WIP Completion]91) * 100

The formula is slightly different to yours as you are dividing the ticked items by the unticked, which isn't an accurate percentange.

The other advantage of using the sheet summary is that you can amend the formula to the columns rather than needing to a range, for example the previous formula would be amended to:

=COUNTIF([Quarterly WIP Completion]:[Quarterly WIP Completion], 1) / COUNT([Quarterly WIP Completion]:[Quarterly WIP Completion]) * 100

If necessary you could then use a sheet summary report to get all the metrics out into a dashboard.

Nick Korna

Hi @Georgia Wyllie,

Can you give a screenshot of the column/your current formula?

Georgia Wyllie

This is the current formula. But I think the issue is with the formatting of the columns. As they are all tick box only I cant seem to total the column.

Nick Korna

Ah - you're trying to do the sum at the "end" of the column, and getting the boolean error there.

Have you considered using the sheet summary instead?

=COUNTIF([Quarterly WIP Completion]1:[Quarterly WIP Completion]91, 1) / COUNT([Quarterly WIP Completion]1:[Quarterly WIP Completion]91) * 100

The formula is slightly different to yours as you are dividing the ticked items by the unticked, which isn't an accurate percentange.

The other advantage of using the sheet summary is that you can amend the formula to the columns rather than needing to a range, for example the previous formula would be amended to:

=COUNTIF([Quarterly WIP Completion]:[Quarterly WIP Completion], 1) / COUNT([Quarterly WIP Completion]:[Quarterly WIP Completion]) * 100

If necessary you could then use a sheet summary report to get all the metrics out into a dashboard.

Georgia Wyllie

Thank you! This resolved everything. I have put the formula into the summary page to view my totals.