Help with date range formula

Hi,

I have a sheet with a "Helper Date" column and a column which shows if a participant registered for an event or not. I'm looking for the total of registrants (attended or not) during a quarter of a year. I have two formulas that work now:

The amount of events per quarter:

=COUNTIFS([Helper Date]:[Helper Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))

And the amount of registrants:

=COUNT(Attended:Attended)

Any suggestions as to how I would combine these formulas to get the amount of registrants during a particular quarter?

Thanks in advance for any suggestions!


Best Answers

  • John Stanik
    John Stanik ✭✭
    Answer βœ“

    Thanks again @Jeff Reisman

    I think I got it. I had a separate "Date Attended" column that I could use to write:

    =COUNTIFS([Event Date]:[Event Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))

    And then get the actual attended and no-shows with:

    =COUNTIFS([Event Date]:[Event Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), Attended:Attended, ="Yes")

    =COUNTIFS([Event Date]:[Event Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), Attended:Attended, ="No")

    I appreciate the assist... still learning 😬

    John

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer βœ“

    Cool! Glad you got it figured out.

    I'm learning new things every day. I've actually been on this board more frequently of late because I'm trying to decide on a new approach in a Smartsheet project redesign, and I'm hoping that helping folks out with their questions might stimulate some answers from my subconscious and get me on the right track!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/10/22


    Try this:

    =COUNTIFS([Helper Date]:[Helper Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), Attended:Attended, OR(@cell = "Yes", @cell = "No))

    Logic: Count if the Helper Date is between these two dates, and the Attended value is Yes or No (but not blank.)

    Edit: you need the helper date populated on each row for this to work!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • John Stanik
    John Stanik ✭✭

    @Jeff Reisman Thanks for the reply. I do have blanks. Is there a way to ignore the blanks in the Helper Date column too?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/10/22

    The COUNTIFS formula doesn't count blanks. In your case, without a value in the Helper Date column, the row won't be counted at all, including the Yes or No in the Attended column. In order to be counted in the COUNTIFS, the row must meet all the conditions. That's why you need the helper date populated for this formula to work for your purposes.

    Is there some reason you don't want to populate that column for all rows? I see you're using parent/child rows, but that shouldn't stop you from putting values in that field for all rows. (I generally hide my helper columns unless it's data my users need to see on the regular.)

    You may want to look into using either the CHILDREN or PARENT functions to help you. I've never used them but they might help do what you want.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • John Stanik
    John Stanik ✭✭

    Thank you @Jeff Reisman. I'm working on a CHILDREN formula now - I'll close this out if it works. Thanks again!

  • John Stanik
    John Stanik ✭✭
    Answer βœ“

    Thanks again @Jeff Reisman

    I think I got it. I had a separate "Date Attended" column that I could use to write:

    =COUNTIFS([Event Date]:[Event Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)))

    And then get the actual attended and no-shows with:

    =COUNTIFS([Event Date]:[Event Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), Attended:Attended, ="Yes")

    =COUNTIFS([Event Date]:[Event Date], AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), Attended:Attended, ="No")

    I appreciate the assist... still learning 😬

    John

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer βœ“

    Cool! Glad you got it figured out.

    I'm learning new things every day. I've actually been on this board more frequently of late because I'm trying to decide on a new approach in a Smartsheet project redesign, and I'm hoping that helping folks out with their questions might stimulate some answers from my subconscious and get me on the right track!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!