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

01/14/22 Edited 01/15/22
Answered - Pending Review

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!

Tags:

Answers

  • 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

  • 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.Genevieve P. admin
    edited 01/18/22

    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

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


Sign In or Register to comment.