% of checked off tasks by task category

Hello!

I'm attempting embed a tracker for understanding progress completion by different categories within a project plan. See screenshot below!


This formula is supposed to count if a task is checked off within the hub and spoke category / total number of items (checked or checked) within that category to give me a percent complete. Does that make sense? The formula i have in the screenshot is for the total percent complete (regardless of task category) and that seemed to work but I cant figure out the percent by category addition!

Thanks for your help!

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    You should be able to do this with a similar structure to what you already have. Something like this should work:

    =COUNTIFS(Done15:Done73, @cell = true, [Task Category]15:[Task Category]73, "Education & Research") / COUNTIF([Task Category]15:[Task Category]73, "Education & Research")

    Where the first COUNTIFS is counting anytime Done in the range you defined is checked AND the task category is the category you're looking for (for the example I used Education & Research, but you can change this to whichever category you're looking for).

    The denominator is a count of how many tasks fall into your category. In this example how many tasks are Education & Research. That should give you the percentage of tasks checked Done in that category.

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    You should be able to do this with a similar structure to what you already have. Something like this should work:

    =COUNTIFS(Done15:Done73, @cell = true, [Task Category]15:[Task Category]73, "Education & Research") / COUNTIF([Task Category]15:[Task Category]73, "Education & Research")

    Where the first COUNTIFS is counting anytime Done in the range you defined is checked AND the task category is the category you're looking for (for the example I used Education & Research, but you can change this to whichever category you're looking for).

    The denominator is a count of how many tasks fall into your category. In this example how many tasks are Education & Research. That should give you the percentage of tasks checked Done in that category.

  • Drea Mora
    Drea Mora ✭✭✭✭
  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Awesome, glad it's working.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!