To count completed tasks in Google Sheets, we can use the COUNTIF function as a combination with some other functions.

You may (not all of you) think that you can use COUNTIF or DCOUNTA alone for the same. But it is not so. It will only be possible if a task has no subtasks.

For example, assume a column contains the string “Complete” or tick boxes to show completed activities.

If it’s column C, you can try the below formula.

=countif(C1:C,"Complete")

Or;

=DCOUNTA(C1:C,1,{"Status";"Complete"})

In the DCOUNTA, I have assumed the field label in C1 is “Status” because it’s a database formula and requires a header row.

Note:- If you have tick boxes, then replace “Complete” in the formulas with TRUE. Do not enclose the TRUE within double quotes as it is a logical value.

If you have multiple groups and want to count only if every row in each group has completed status, then the above formulas won’t come in handy.

In this Google Sheets tutorial, I have two types of formula solutions for you. Both are using COUNTIF. But the supporting functions will be different.

Time to go through an example.

Sample Data and Desired Count

Let’s see how to count completed and incomplete tasks, here actions, based on the status column C in Google Sheets.

The above sample data shows the inquiries received from multiple customers for an item and its statuses with the supplier now.

A company received inquiries from four customers – Customer A, B, C, and D.

Except for company B, the orders of the other three companies have already been fulfilled/materialized.

How to count the status column if only all the statuses against a company show “Complete”?

As per the above data, the desired results are as follows.

Completed Action – 3

Incomplete Action – 1

Formulas to Count Completed Tasks in Google Sheets

First, we must find a way to count the total number of completed tasks. Then, it will be easy for us to find the total number of incomplete tasks.

Because;

Total Incomplete Tasks = Total Unique Tasks - Total Completed Tasks

You can find more details under the title “Formulas to Count Incomplete Tasks in Google Sheets” at the end.

Formula Option # 1

Here is the formula to use to count the total fully completed tasks.

=ArrayFormula(countif(IFNA(vlookup(UNIQUE(A2:A),sort(A2:C,3,0),3,0)),"Complete"))

Insert the above formula in cell F2, which will return 3 based on the above sample data (image # 1).

The COUNTIF is the key, but we have to prepare the data before using it. The VLOOKUP does that with the help of UNIQUE and SORT.

Here are the explanations for each step involved in the above formula to count completed tasks in Google Sheets.

Explanation

Formula Part # 1: =UNIQUE(A2:A)

The above formula will return unique customers.

Formula Part # 2: =sort(A2:C,3,0)

The above SORT formula sorts the third column (status column) values in descending order. So the rows marked with the “Incomplete” action will be on the top.

Formula Part # 3: vlookup(UNIQUE(A2:A),sort(A2:C,3,0),3,0)

The formula part 3 is the VLOOKUP. The above formula part # 1 acts as the search_keys in VLOOKUP, and the formula part # 2 acts as its range.

The VLOOKUP will return the values from the third column from this range.

In a first column unsorted range, the formula will return the values from the first occurrence.

That means, the formula will return the values as below (second column in the table).

Table # 1

Using COUNTIF in these two columns, we can count the number of completed tasks in Google Sheets.

Used IFNA to remove the #N/A error and the COUNTIF to count column 2 if the value is “Complete”.

Formula Option # 2

Compared to the above, this is a shorter formula.

=countif(sortn(sort({A2:A,C2:C},2,0),9^9,2,1,0),"Complete")

As I have said many times, SORTN is one of the best functions in Google Sheets. See how useful it is here.

Let’s understand the formula parts.

Here the earlier formula part # 1 (unique) is not applicable.

The formula part # 2 (sorting) is here. It only requires columns A and B in sorting.

So used {A2:A,C2:C} instead of A2:C in sorting.

Here also sorts the status column C in descending order. Please see image # 2 above for the sort result. The column H in sort result won’t be present here.

The SORTN removes the duplicate customer rows based on column 1. So the result will be similar to table # 1 above.

Here also, the COUNTIF does the rest of the part.

You have got two formula options to count completed tasks in Google Sheets. What about counting uncompleted activities?

Formulas to Count Incomplete Tasks in Google Sheets

I have already given one generic formula just above Formula Option # 1 (subtitle). Please refer to that first.

Using COUNTUNIQUE(A2:A) we can get the total count of unique customers or tasks. As per our sample, it will be 4.

From this, deduct the output of the above result.

So the formula using Formula Option # 1 will be;

=COUNTUNIQUE(A2:A)-ArrayFormula(countif(IFNA(vlookup(UNIQUE(A2:A),sort(A2:C,3,0,1,1),3,0)),"Complete"))

And the Formula Option # 2 will be;

=COUNTUNIQUE(A2:A)-countif(sortn(sort({A2:A,C2:C},2,0),9^9,2,1,0),"Complete")

We can use either of the above formulas to count the incomplete tasks for the sample data given in Google Sheets.

That’s all! Enjoy.

Related Reading: Mark Tasks as Complete Once All the Subtasks Finished in Sheets.