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
-
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
-
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
-
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!
-
@Jeff Reisman Thanks for the reply. I do have blanks. Is there a way to ignore the blanks in the Helper Date column too?
-
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!
-
Thank you @Jeff Reisman. I'm working on a CHILDREN formula now - I'll close this out if it works. Thanks again!
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!