Count Open Tasks by Owner with Google Sheets QUERY
How do I count open tasks by owner in Google Sheets using QUERY?
Use QUERY to filter for Open status, leave out blank owners, group by owner, and count populated task IDs. This gives you assigned open task records, not a distinct task count or an estimate of effort.
To count open tasks by owner in Google Sheets, use QUERY to filter for Open status, group by owner, and count populated task IDs. The formula below leaves out blank owners, so it reports assigned open tasks, not every open task. Google’s reference explains the filtering, grouping, and counting clauses behind the formula.
What source columns do I need to count open tasks by owner?
Put task IDs in column A, owners in column B, and statuses in column C, with one header row above the records.
| Column | Header | What belongs here |
|---|---|---|
| A | Task ID | A nonblank identifier for each task |
| B | Owner | One consistent owner name per task |
| C | Status | A status value, such as Open |
Use a separate row for each current task record. An owner and an open status alone will not make that row count: the task ID must also be populated. That’s because count(A) counts non-null values.
Copy the sample into A1:C9. These names are fictional, and the blank owner on T-107 is intentional.
| Task ID | Owner | Status |
|---|---|---|
| T-101 | Alex Fiction | Open |
| T-102 | Morgan Fiction | Open |
| T-103 | Alex Fiction | Closed |
| T-104 | Taylor Fiction | In progress |
| T-105 | Morgan Fiction | Open |
| T-106 | Alex Fiction | Open |
| T-107 | Open | |
| T-108 | Taylor Fiction | Open |
Leave source cells empty when there is no value to record, and stick to a consistent ID format such as T-101. Google’s QUERY documentation explains that a column’s majority data type determines its type; QUERY treats minority types as null. A mix of numeric and text IDs can therefore change the count.
How do I run an SQL query in Google Sheets?
Enter a QUERY function using Google’s query language to get this owner count. As Google explains in its language reference, the language is a subset of SQL, not a full SQL implementation.
-
Put the source headers in row 1. Enter
Task IDin A1,Ownerin B1, andStatusin C1. -
Give each task a separate row. Follow the sample’s column order.
-
Set E1 as the output cell. Leave columns E and F empty so the owner names and counts have room.
-
Paste the following formula into E1.
=QUERY(A1:C100,"select B, count(A) where C = 'Open' and B is not null group by B label count(A) 'Open tasks'",1)
The final 1 tells the function that the source has one header row. Without that argument, QUERY infers the headers, as described in the function documentation.
Wrap the query text in double quotes, keeping single quotes around 'Open' and 'Open tasks'. Those strings belong inside the query language syntax. They are not separate function arguments.
To place the report on another tab, use Tasks!A1:C100 as the data argument if your source tab is named Tasks. Leave the query text alone. The function’s data argument supplies the range; the query continues to refer to columns A, B, and C.
What counts should the sample produce?
The sample should return two qualifying records for Alex Fiction, two for Morgan Fiction, and one for Taylor Fiction.
| Owner | Open tasks |
|---|---|
| Alex Fiction | 2 |
| Morgan Fiction | 2 |
| Taylor Fiction | 1 |
For Alex Fiction, T-101 and T-106 give 1 + 1 = 2. For Morgan Fiction, T-102 and T-105 give 1 + 1 = 2. Taylor Fiction’s count includes T-108, but not T-104: its status is In progress.
Together, the assigned counts total 2 + 2 + 1 = 5. The source contains six open tasks. Leaving out T-107 because its owner is blank gives 6 - 1 = 5 assigned tasks, which is exactly what the query’s B is not null condition requires.
I would call this report “Assigned open tasks” rather than “All open tasks.” The latter could hide the unassigned task during a workload review. Check for unassigned records separately by adapting the method for flagging CRM contacts without an owner or next step to the task list.
How does each QUERY clause control the owner count?
The clauses decide which task records qualify, group those records by owner, and count their populated IDs. Google’s where, group by, and aggregation rules define how those operations work.
| Formula component | Effect |
|---|---|
A1:C100 |
Reads the header and task records through row 100 |
select B, count(A) |
Returns each owner and a count of populated task IDs |
where C = 'Open' |
Keeps rows whose status matches Open exactly |
and B is not null |
Removes rows with blank owners |
group by B |
Combines matching rows into one result per owner |
label count(A) 'Open tasks' |
Gives the count column a readable heading |
Final 1 |
Declares that the source has one header row |
The range and header argument go to the Sheets function. The query text inside the double quotes tells it which records to include and which values to return.
Refer to columns by their identifiers, not the header labels you see in the sheet. Changing the header from Owner to Assignee does not change column B. If you move the owner data to a different column, though, you must update the query’s column identifiers.
Read count(A) as a count of populated IDs, not distinct IDs. Two qualifying rows containing the same populated ID will both count because count() counts elements and excludes nulls. Before deleting repeated IDs, check whether they are accidental duplicates or records of task history.
How can I include tasks added below row 100?
Extend the formula’s data range to pick up tasks below row 100. QUERY reads only the range you give it through the data argument.
=QUERY(A1:C,"select B, count(A) where C = 'Open' and B is not null group by B label count(A) 'Open tasks'",1)
The original A1:C100 range allows for one header row and 99 potential task rows. A task on row 101 stays out of the result, even when its owner and status satisfy the query conditions.
If you use the open-ended range, keep columns A through C reserved for task records. A note entered in those columns with an owner and the word Open could otherwise be counted as a task.
I prefer setting the range policy before an understated count draws attention to it. A bounded range suits a sheet with a fixed reporting boundary. Use the open-ended version when the columns are reserved for a task list that will keep growing.
Why are open-task counts missing or incorrect?
Missing or incorrect open-task counts usually call for checking the filter, the task ID, and the source range: a record may fail the conditions, have an ID QUERY cannot count, or fall outside the range.
Check status text first. The query language uses case-sensitive comparisons, according to Google, so open will not match Open. Look for spaces around the status as well. Correct accidental variations in the source instead of widening the report to accept them.
Then check the owner names. The grouping rules use the values in column B, so variations of a name can split the same owner’s tasks into separate groups. Fix the name in the source rather than manually combining rows in the output.
If a record appears to qualify but adds nothing to the count, inspect column A. count() cannot count nulls, and QUERY treats minority data types as null. Supply missing IDs and make sure the ID values follow a consistent format.
Repeated task IDs that represent status history need a separate data preparation step. If you count those history rows, the report starts from the wrong records. Prepare the source with SQL ROW_NUMBER to keep the latest status row per task.
For a spreadsheet locale that uses semicolons between function arguments, enter this version:
=QUERY(A1:C100;"select B, count(A) where C = 'Open' and B is not null group by B label count(A) 'Open tasks'";1)
Leave the comma in select B, count(A) as it is. That comma separates selected expressions within the query language; it does not separate function arguments. If copying changed the quotation marks, replace them with straight quotes.
When should I use something other than this owner count?
Use a different report structure when you need zero-count owners, shared assignments, interactive grouping changes, or effort estimates.
| Requirement | Better approach |
|---|---|
| Include owners with zero open tasks | Maintain a separate owner list with COUNTIFS beside it |
| Change groupings without editing query text | Use a pivot table, including multiple statuses as needed |
| Count each owner in shared assignments | Create an assignment table with one task-owner pairing per row |
| Compare effort rather than task counts | Record an effort field using a consistent estimation method |
This query has no complete owner roster to work from. It groups only records that survive the filter. Google’s filtering and grouping rules therefore produce no row for an owner without matching records, rather than a row showing zero.
A cell containing Alex Fiction, Morgan Fiction supplies one grouping value, not two assignments. The group by clause groups column values as written. It does not break a list of names into individual people.
I would report task counts separately from workload estimates. Two open tasks can take different amounts of work, and this source has no effort field to capture that difference. The spreadsheets and data topic page covers calculations with other units and reporting boundaries.