CountIFS for a Summary Field

Hi All,

Need a little help with a Count IFs statement for a summary field.

I'm trying to count the number of rows in my sheet by Due Date and Ancestor Level. There are a limited number of due dates so I plan to create a summary field for each one. The two columns involved are due date and Ancestors. I only want to know Ancestors=1 and count for each due date. Here is what I have to count for June 5.

=COUNTIFS([Due Date]:[Due Date], "06/05/22", Ancestors:Ancestors, "1")

I'm getting a value of 0, which is not correct. I think it must be something in the way that it is recognizing the date (column type is date). Do I need to use a special date function to count/match date values?

Thanks in advance!

Ann

Best Answer

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓

    Hi

    The problem may be the date part of your formula. Try using the @cell and Date to reference your Due Date.

    =COUNTIF([Due Date]:[Due Date], @cell = DATE(2022, 6, 6))

    I am not sure what Ancestors is but as long as it's a text field with a "1" in there somewhere you should be fine including them both with a COUNTIFS

    I hope this helps

    Kevin

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓

    Hi

    The problem may be the date part of your formula. Try using the @cell and Date to reference your Due Date.

    =COUNTIF([Due Date]:[Due Date], @cell = DATE(2022, 6, 6))

    I am not sure what Ancestors is but as long as it's a text field with a "1" in there somewhere you should be fine including them both with a COUNTIFS

    I hope this helps

    Kevin

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Wonderful. It worked.

    Thanks, Kevin!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!