Percentage of ticked boxes completed. Column total.

Options
edited 01/02/24

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!