Percentage of ticked boxes completed. Column total.

Options
Georgia Wyllie
edited 01/02/24 in Formulas and Functions

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @Georgia Wyllie,

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

  • Georgia Wyllie
    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.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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.

  • Georgia Wyllie
    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!