Count if Between a date range

Bobhope84
Bobhope84 ✭
edited 9:20AM in Formulas and Functions

Morning Smartsheet community, Hope you all had a great festive period.

I'm struggling with a formula that would make my reports a whole lot easier…..

I have a helper column that I want to return a value@row could be anything but for ease say "1", If the date in the [Form Date]@row is between the dates set in [PP Dates]1 and [PP Dates]2

Im guessing its possible but my brain is struggling to see through the fog this morning

Thanks for any input

Tags:
· Share on FacebookShare on Twitter

Answers

  • Protonsponge
    Protonsponge ✭✭✭✭✭✭

    Hello @Bobhope84,

    Could the following formula be what you are looking for?

    =COUNTIFS([Form Date]:[Form Date], >=[PP Dates 1]@row, [Form Date]:[Form Date], <=[PP Dates 2]@row)

    As per the demo below, the above formula will COUNTIFS the dates in [Form Date] is equal to or greater than [PP Dates 1] and equal to or less than [PP Dates 2]

    image.png

    You could also set this up in the sheet summary if it was more useful for your reporting as per below.

    =COUNTIFS([Form Date]:[Form Date], >=[PP Dates 1]#, [Form Date]:[Form Date], <=[PP Dates 2]#)

    image.png

    I hope that is helpful to you in some way,

    Protonsponge

    · Share on FacebookShare on Twitter
  • Protonsponge
    Protonsponge ✭✭✭✭✭✭
    edited 9:35AM

    Hello @Bobhope84

    I just re-read your question and realised I did not answer your question, clearly I have Friday brain fog also and I have yet to have the morning coffee.

    Upon re-reading your question, are you more looking for something like this?

    =IF(AND([Form Date]@row >= [PP Dates 1]$1, [Form Date]@row <= [PP Dates 2]$1), 1, 0)

    image.png

    Sorry for the initial mis-understanding and I hope something in the above posts is helpful to you… off for that coffee now :-)

    Protonsponge

    · Share on FacebookShare on Twitter
  • Bobhope84
    Bobhope84 ✭
    https://community.smartsheet.com/discussion/comment/467803#Comment_467803

    Thanks for the reply.

    So Kinda, But not quite…..

    This formula will count the total entries with the date range between the specified dates

    =COUNTIFS([Form Date]:[Form Date], >=[PP Dates]1, [Form Date]:[Form Date], <=[PP Dates]2)

    The bit I'm struggling with is I'd like it to return something in the helper column @row so I can filter/Report using the column

    I have 30 reports that I need to change the value of the filter dates each month so my plan is i change the filter to look at the helper instead of the column and I can change one date range on the sheet ([PP dates]1 & pp Dates]2) and then all the reports are updated to reflect the new dates…. if that makes sense?

    · Share on FacebookShare on Twitter
  • Protonsponge
    Protonsponge ✭✭✭✭✭✭

    @Bobhope84 - really sorry for mis-reading your initial Q, was the 2nd post what you were looking for?

    Protonsponge

    · Share on FacebookShare on Twitter
  • Bobhope84
    Bobhope84 ✭
    https://community.smartsheet.com/discussion/comment/467805#Comment_467805

    =IF(AND([Form Date]@row >= [PP Dates]1, [Form Date]@row <= [PP Dates]2), 1, 0)

    So this is the formula that worked, Thanks for your help.

    Hope you enjoy your morning coffee, You've earned it

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!