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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!