Task Management - Total Count for Section when Adding/Deleting Rows

Kenya Asbill

Good morning!

How do I track the total count for a section of rows without having to manually update the formula if a row is added or deleted from the section?

For example, I have a section with this formula -

=COUNTIF([Include in Total Task Count]2:[Include in Total Task Count]10, "Yes")

If I add a row to the section, is there a way for the formula to be automatically updated?

=COUNTIF([Include in Total Task Count]2:[Include in Total Task Count]11, "Yes")

Or

If I delete a row from the section, is there a way for the formula to be automatically updated?

=COUNTIF([Include in Total Task Count]2:[Include in Total Task Count]9, "Yes")

Thank you for your help!

Genevieve P.

Hi @Kenya Asbill

Instead of identifying specific rows in your range (ex from row 2 to row 10), you'll want to reference the entire column. Then as new rows are added or deleted they will be automatically included in your formula.

=COUNTIF([Include in Total Task Count]:[Include in Total Task Count], "Yes")

See: Create a Cell or Column Reference in a Formula

However if there are rows you want to exclude from this count, so you don't want the entire column to be referenced, you could use a COUNTIFS plural, to identify the criteria you don't want included. (See: COUNTIFS Function)

For example, say Row 1 has a title of "Summary Row" in your Task Name column. You could make sure that Row 1 is not included in the Count by adding this as a criteria:

=COUNTIFS([Include in Total Task Count]:[Include in Total Task Count], "Yes", [Task Name]:[Task Name], <> "Summary Row")

Or, if you always know the top row will have a "Yes" that needs to be excluded, you could just subtract 1 from the total:

=COUNTIF([Include in Total Task Count]:[Include in Total Task Count], "Yes") - 1

Let me know if this makes sense and will work for you! If not, it would be helpful to see a screen capture of your sheet to identify how best to build this formula, but please block out sensitive data.

Cheers,

Genevieve

Kenya Asbill

Good morning @Genevieve P. ! Thank you for the response! I am familiar with referencing an entire column as I already have a total count in the sheet. I also, however, have several sections that I need to keep a total count for so how would I track that count especially when needing to add/delete rows from certain sections? Please see example screenshot below for adding a row.

Genevieve P.

Hi @Kenya Asbill

If I'm understanding you correctly, your tasks as Child Rows indented beneath a Parent row, and you want the Parent row to count how many of the Children have a date in them, is that correct?

If so, you can use the CHILDREN function! Try something like this:

=COUNT(CHILDREN()) + " of " + COUNT(CHILDREN([Task Name]@row))

This will tell you how many non-blank cells there are in the current column beneath your top, Parent row, versus how many non-blank cells there are beneath your Parent row in the Task Name column.

Then if you add more Children rows, it will automatically add them in to your Counts. See: CHILDREN Function

Cheers,

Genevieve

Kenya Asbill

@Genevieve P. Hello! Some of the children have children and not all of the rows should be included in the count and subcounts.