Counting Incomplete Past Due Activities by Category

Hi all,


I'd like to get a total count of the activities in my project plan which meet the following criteria:

1) "Expected Due Date" is past due (<= Today)

2) "Expected Due Date" is not blank

3) "Done?" is unchecked

4) "Category" is "ABC"

I assume I'll need a check box column with a formula to indicate if these criteria are met, followed by a count formula to calculate the number of boxes checked. Any recommendations are greatly appreciated!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hello @JazzyJ

    The COUNTIFS formula will provide this count for you. If desired, you could add this formula to a Sheet Summary field (found in right hand menu on sheet). I am assuming this formula is within the same sheet that you are counting - vs putting the formula in a different sheet and pulling the values as a cross sheet reference)

    You will need to substitute your actual column names into this formula.

    =COUNTIFS([Expected Due Date]:[Expected Due Date], <=Today(), [Expected Due Date]:[Expected Due Date],ISDATE(@cell), [Done?]:[Done?], 0, Category:Category, "ABC")

    cheers


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hello @JazzyJ

    The COUNTIFS formula will provide this count for you. If desired, you could add this formula to a Sheet Summary field (found in right hand menu on sheet). I am assuming this formula is within the same sheet that you are counting - vs putting the formula in a different sheet and pulling the values as a cross sheet reference)

    You will need to substitute your actual column names into this formula.

    =COUNTIFS([Expected Due Date]:[Expected Due Date], <=Today(), [Expected Due Date]:[Expected Due Date],ISDATE(@cell), [Done?]:[Done?], 0, Category:Category, "ABC")

    cheers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!