Count Open Tasks by Owner with Google Sheets QUERY

What source columns does Google Sheets QUERY need to count open tasks by owner?

Google Sheets QUERY needs a task ID, an owner, and a status column to count assigned open tasks by owner with this formula.

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

The query counts populated task IDs, not every row containing an owner. Keep IDs filled and consistently formatted.

Sample task list

Enter this sample in A1:C9. The names are fictional; the blank owner for 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 unused source cells blank. Referencing through row 100 is valid even though this sample ends at row 9.

How do you set up the open-task count in Google Sheets?

Place the Google Sheets QUERY formula in E1, outside the source range, with columns E and F clear for its two-column result.

1. Add source headers in row 1. Enter Task ID in A1, Owner in B1, and Status in C1. The formula assumes one header row.

2. Enter one task per row. Use consistent text IDs such as T-101 and identical owner spelling.

3. Select E1 for output. Leave enough empty space in columns E and F for every owner and the output headers.

4. Paste this 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)

5. Press Enter and compare with the expected output below. Do not add separate output headers; QUERY supplies them.

6. Check how source changes affect the count. Changing T-103 from Closed to Open should increase Alex Fiction’s count from 2 to 3. Reversing that change should restore 2.

Only E1 contains the formula; edit E1 rather than individual result cells. For a separate reporting tab, replace A1:C100 with Tasks!A1:C100 if the source tab is named Tasks.

What open-task counts should the sample produce?

The unchanged sample produces two open tasks for Alex Fiction, two for Morgan Fiction, and one for Taylor Fiction.

Owner	Open tasks
Alex Fiction	2
Morgan Fiction	2
Taylor Fiction	1

Alex Fiction’s qualifying tasks are T-101 and T-106; the closed task is excluded. Morgan Fiction has two open tasks, while Taylor Fiction’s In progress task does not qualify.

The sample contains six open tasks, but the output totals five because T-107 has no owner. This report counts assigned open tasks. Owners without matching open tasks are omitted, not listed with zero.

Why does QUERY with group by B and count(A) count open tasks by owner?

QUERY with group by B and count(A) counts open tasks by owner because it filters qualifying records, groups them by owner, and counts their populated task IDs.

First, QUERY reads A1:C100, a bounded range that includes the sample and room for additional records without reading entire columns. The final argument, 1, identifies the source header row explicitly rather than leaving header detection to inference.

Next, where C = 'Open' and B is not null keeps assigned open tasks rather than summarizing every status. group by B then collects those records into one group per owner, replacing a task-by-task listing with an owner-level summary.

For each group, select B, count(A) returns the owner and counts populated IDs in column A. Counting IDs ties the measure to task records rather than merely counting owner entries. Finally, label count(A) 'Open tasks' supplies a readable count heading instead of an automatically generated aggregation label. The unchanged sample therefore yields Alex Fiction with 2, Morgan Fiction with 2, and Taylor Fiction with 1.

How does each QUERY clause control the owner count?

The Google Sheets QUERY formula filters for Open, excludes blank owners, groups by owner, and counts populated task IDs.

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

Query references use column letters, not header names. Renaming Owner to Assignee does not change B; moving owner data requires updating the reference.

Keep the outer double quotes and the single quotes around 'Open'. The aggregation is not distinct: duplicate task records with populated IDs can each contribute.

How can the QUERY summary include new tasks and consistent status values?

Use an open-ended source range in Google Sheets QUERY to include tasks added below row 100, and dropdowns to control status and owner values.

=QUERY(A1:C,"select B, count(A) where C = 'Open' and B is not null group by B label count(A) 'Open tasks'",1)

A1:C100 allows one header plus 99 task rows; row 101 is excluded. The open-ended version includes later rows, so keep unrelated notes and tables out of columns A through C.

For large workbooks, a bounded range such as A1:C5000 can limit calculation work while allowing growth.

Select status cells, choose Data > Data validation, and create a dropdown with exact allowed values. An owner dropdown likewise prevents spelling variations from creating separate groups.

How do you fix missing counts and errors in this QUERY formula?

Fix Google Sheets QUERY count problems by correcting source values, clearing blocked output cells, or adjusting formula syntax for the spreadsheet locale.

Open tasks are missing because status text differs

Symptom: Counts are low or no owner rows appear.

Cause: Matching is case-sensitive and exact; open and Open differ from Open.

Exact fix: Replace affected statuses with Open, capital O and no surrounding spaces. For ordinary extra spaces, fill =TRIM(C2) down a temporary column, copy the results, and use Paste special > Values only over the source statuses. Correct capitalization separately.

One owner appears on separate rows

Symptom: Multiple result rows appear to represent one person.

Cause: Owner spellings or spaces differ.

Exact fix: Standardize source names, such as Alex Fiction. For ordinary extra spaces, fill =TRIM(B2) down a temporary column and paste the results into column B as values. Do not edit the query output.

The formula returns an array expansion error

Symptom: E1 displays #REF! because the result would overwrite data.

Cause: Existing content or merged cells block expansion.

Exact fix: Move content out of the required area in E and F, clear the blocking cells, and unmerge cells there. Preserve E1’s formula, or move it to an empty area outside A1:C100 to avoid circular dependencies.

Task IDs are blank or mixed

Symptom: Qualifying open rows exist, but counts are low.

Cause: count(A) ignores null IDs. QUERY can treat minority data types in mixed-type columns as null.

Exact fix: Fill missing IDs and standardize text IDs, such as T-109. Choose Format > Number > Plain text, then re-enter or replace inconsistent IDs; changing formatting alone may not repair existing values.

Google Sheets reports a formula parse error

Symptom: No table appears, and Sheets reports a formula parse error.

Cause: Curly quotes or locale-specific separators may be responsible.

Exact fix: Restore straight quotes. If the locale requires semicolons between function arguments, use:

=QUERY(A1:C100;"select B, count(A) where C = 'Open' and B is not null group by B label count(A) 'Open tasks'";1)

Keep the comma inside select B, count(A); it belongs to the query language.

When should you avoid this owner-level QUERY count?

Avoid this QUERY approach when the report needs a complete owner roster, interactive regrouping, separate counts for shared assignments, or effort-based workloads.

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

QUERY treats Alex Fiction, Morgan Fiction in one cell as one owner value, not two people. Equal task counts also need not mean equal workloads: this formula measures qualifying task records, not assignment complexity.