Percentage of ticked boxes completed. Column total.
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.
Best Answer

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.
Answers

Hi @Georgia Wyllie,
Can you give a screenshot of the column/your current formula?

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.

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.

Thank you! This resolved everything. I have put the formula into the summary page to view my totals.
Help Article Resources
Categories
Check out the Formula Handbook template!