Help with date range formula

John Stanik · 01/10/22

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!

John Stanik · 01/10/22

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 · 01/10/22

https://community.smartsheet.com/discussion/comment/315642#Comment_315642

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!

Jeff Reisman · 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!

John Stanik · 01/10/22

@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 · 01/10/22

https://community.smartsheet.com/discussion/comment/315604#Comment_315604

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.

John Stanik · 01/10/22

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

John Stanik · 01/10/22