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

Kenya Asbill
Kenya Asbill ✭✭
edited 01/15/22 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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. Employee 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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!