Spreadsheet Practice

How to calculate percent complete in Excel

Formula (Excel and Google Sheets)

=COUNTIF(B2:B11,"Done")/COUNTA(B2:B11)

Count the finished tasks and divide by all tasks: =COUNTIF(B2:B11,"Done")/COUNTA(B2:B11), formatted as a percentage.

Result on the example
60% in F1
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.

How it works

COUNTIF counts the six tasks marked Done, and COUNTA counts every task that has a status, ten. Six out of ten is 0.6, which the Percent format shows as 60%.

Every task counts the same here, whether it takes two hours or sixteen.

=COUNTIF(B2:B11,"Done")/COUNTA(B2:B11)

Result0.6

  1. B2:B1110 rows × 1 col: "Done", "Done", "In progress", "Done", …

    A range: 10 cells in one column.

  2. COUNTIF(B2:B11,"Done")6

    COUNTIF: Counts cells that meet one condition.

    • rangeB2:B1110 rows × 1 col: "Done", "Done", "In progress", "Done", …
    • criteria"Done"
  3. COUNTA(B2:B11)10

    COUNTA: Counts cells that are not empty.

    • value1B2:B1110 rows × 1 col: "Done", "Done", "In progress", "Done", …
  4. COUNTIF(B2:B11,"Done")/COUNTA(B2:B11)0.6

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:B11,"Done")/COUNTA(B2:B11). We ran it in Google Sheets mode on the same data and it returns 60%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMIF(B2:B11,"Done",C2:C11)/SUM(C2:C11)38.57%Different resultWeights each task by its hours, which is a fairer measure when tasks differ in size. Returns 38.6%: the finished tasks are the small ones.

Common mistakes

  • COUNTA counts every non-empty status, including "Cancelled". Exclude those from the total: =COUNTIF(B2:B11,"Done")/(COUNTA(B2:B11)-COUNTIF(B2:B11,"Cancelled")).
  • Rows added below B11 aren't included. Format the list as a table, or use a range that runs further down.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate percent complete

In F1, calculate the percentage of tasks in B2:B11 with the status Done.

Questions

How do I show progress as a bar?

Use conditional formatting, Data Bars, on the percentage cell, or =REPT("█",ROUND(F1*10,0)) for a text bar.

How do I calculate percent complete with checkboxes?

Count the TRUEs: =COUNTIF(B2:B11,TRUE)/ROWS(B2:B11).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.