Conditional count of parents rows only with now children on a date column.

Help!

I have this formula on a different sheet that works when I am counting the dates in the column. However, I want to count only the parent deliverables, not the children’s tasks. Can someone please help me?

=COUNTIFS({CC Label Activities}, AND(@cell >= DATE(year@row, [Month1]@row, 1), @cell <= DATE(year@row, [Month1]@row, 31)))

Sample Data Sheet

Sample Formula sheet

Thank you!

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 12/05/23 Answer ✓

    Hi @Kingskid

    Ah my apologies! I skipped over the AND in your COUNTIFS. You don't need this as the COUNTIFS is already an AND statement.

    Try:

    =COUNTIFS({Date Logged}, <> "", {CC Label Activities}, >= DATE(year@row, [Month1]@row, 1), {CC Label Activities}, <= DATE(year@row, [Month1]@row, 31))

    Let me know if this gives you an error or incorrect result.

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Kingskid

    Since we removed the AND(), you only need 2 )) at the end of the formula. Try removing out one of the ) and see if that helps. 🙂

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • Hi @Kingskid

    You can filter out Child Rows as long as your source sheet has something that indicates if the row is a Child or Parent.

    From what I can see in your screen capture, only Parent rows have a date in the "Date Logged" column, is that correct? If so, you can add criteria that says that cell needs to be <> "" or Not Blank:

    =COUNTIFS({Date Logged Column}, <> "", {CC Label Activities}, AND(@cell >= DATE(year@row, [Month1]@row, 1), @cell <= DATE(year@row, [Month1]@row, 31)))

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Hi @Genevieve P. ,

    Thank you for being so helpful.

    Please see the image below.

    Thank you!

  • Genevieve P.
    Genevieve P. Employee
    edited 12/05/23 Answer ✓

    Hi @Kingskid

    Ah my apologies! I skipped over the AND in your COUNTIFS. You don't need this as the COUNTIFS is already an AND statement.

    Try:

    =COUNTIFS({Date Logged}, <> "", {CC Label Activities}, >= DATE(year@row, [Month1]@row, 1), {CC Label Activities}, <= DATE(year@row, [Month1]@row, 31))

    Let me know if this gives you an error or incorrect result.

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Hi @Genevieve P.

    Sorry but I am getting the error below. I tried to play around with it, but I still get errors.

    Thank you for your time.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Kingskid

    Since we removed the AND(), you only need 2 )) at the end of the formula. Try removing out one of the ) and see if that helps. 🙂

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Kingskid
    Kingskid ✭✭
    edited 12/05/23

    Hi @Genevieve P. ,

    Thanks a million for your help! I appreciate it!

    It worked! 😂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!