How to calculate percentage of completed tasks in Numbers spreadsheet?

I'm not sure whether the blank you mentioned is truly empty, or if it's actually filled in with the word "Blank."

If the cells are truly empty, you can use the following formula to calculate:

=COUNTIF(E3:E9,"Completed") / (ROWS(E3:E9) * COLUMNS(E3:E9))

If the unfinished items are filled in with "Blank" or "Not Completed" or whatever, you can directly use this formula:

=COUNTIF(G3:G9,"Completed") / COUNTA(G3:G9)

There are two problems with your formula.

The first is that your definition for the ROWS() function is invalid:

Did you manually type "List 1::"? because that's not how it should be done.

IF it's needed, the table name should be displayed within the bubble:

You can correct this by deleting the 'List 1::' text that's there. This should result in the correctly formatted reference. The table name is only needed if you're referencing a different table (which doesn't look like the case here?).

Even so, the second problem is that this won't work for your layout.

As noted, your layout is very 'Excel-like', using blank rows to delineate chunks of data. Technically there's nothing wrong with this, but it will affect your calculation. In this case, you're dividing the number of 'Completed' entries by the number of rows in the table. The ROWS() command will return the complete row count, including those blank rows.

For example, from what I can see, your table has 255 rows. There might be 100 assignments to complete, and let's assume every single one of them might be done.

=COUNTIF(C,"Completed")

returns 100

=ROWS(C,1)

returns 255

So your calculation results in 100/255, or a ≈39% completion ratio.

In this case you're better off comparing the number of 'Completed' to the number of assignments, which can be determined via:

=COUNTA(B)

which counts the number of non-blank cells in column B (the assignment type).

So, your percentage should look something like:

=COUNTIF(C,"Completed")/COUNTA(B)

Not sure how my post disappeared. Here it is again.

Two ways to get you started. The main idea is to count the completed, divide that by the total count, and format the result as Percentage.

1 - Using Checkbox:

=COUNTIF(A,TRUE)/COUNTA(A)

2- Using Pop-Up Menu

=COUNTIF(A,"Complete")/ROWS(List 1::A,1)

Use ; instead of , in the formula if your region uses , as the decimal separator.

SG

If your popups are in column A, this formula will count how many say "completed"

=COUNTIF(A,"completed")

This formula will give you a count of how many rows there are, not counting headers and footers

=ROWS(A,1)

Put those two together to get the percentage completed, assuming all of column A is popups:

=COUNTIF(A, "completed")/ROWS(A,1)

Format the cell as percentage

SAHelpMe wrote:

Unfortunately, numbers doesn't like the formula? please see screenshot below.

Your screensheet looks nothing like the “Number like” example I provided. The screenshot looks very “Excelsy”.

If you haven’t done so already I highly recommend having a look at the built-in templates to see examples of effective Numbers document design. There’s even a Checkmark Totals template that gets you part of the way toward what you are trying to do.

SG

The format is unlike what I would use in Numbers (or indeed Excel). Technically not wrong. But not an effective use of the tool. Highly recommend learning a little about Numbers and having a look at the templates, especially the Checklist templates.

SG