CountIf with Date range

1235

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Linda_T_D If you are still getting the same error then you need to make sure the first cross sheet reference is also created properly.

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • @Paul Newcome If you're not tired of me yet... :)

    I have used the same method for the cross-sheet reference in both of these instances. The first formula returns #INVALID REF and the second returns the number I want, but will not adjust to the dates, obviously, which is what I need. Any other advice on this?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Linda_T_D That particular error comes from having a cross sheet reference not properly set up. If you click into the cell as if you are going to edit the formula and then drop the cursor inside of the {Run Date} range, what is in the little formula helper box? Is it "Reference Another Sheet" or "Edit Reference"?

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • @Paul Newcome Thank you for holding my hand through this!

    It said "Reference Another Sheet"

    So I linked the "Run Date" column from the source sheet.

    I set up the source sheet to return "3" but now I'm getting a "0" result.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Linda_T_D Ok. What happens if you remove the first range and criteria so that it is only a COUNTIFS for the date range?

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Linda_T_D Ok. SO we have a problem with the dates. Did you ensure that all three are set as date type columns? If so, exactly how are each of the three being populated?

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • @Paul Newcome Thank you so much for hanging in there with me on this!

    The "Start Date" and "End Date" columns are being populated by links to another sheet, and you're correct, they were not set up as date-type columns. This seems to have solved the issue! I'm learning as I go and this was a huge help.

    Thank you again!!

  • @Paul Newcome Me again! 😬

    So this seems to be counting only if there's one selection in the cell. There will always be multiple selections in the cells in this column. Can you help, please?

    =COUNTIFS({Shared Content Planning_Tracking Range 1}, "Turrell Fund", {Shared Content Planning_Tracking Range 2}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Linda_T_D To accommodate searching through multiple selections in a cell you would need to incorporate a HAS function like so:

    =COUNTIFS({Shared Content Planning_Tracking Range 1}, HAS(@cell, "Turrell Fund"), {Shared Content Planning_Tracking Range 2}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • @Paul Newcome Thank you so much! This is going to change my life! :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Linda_T_D Happy to help. 👍️

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • Hi! I have read through this thread for tips but am still getting an #unparseable error when referencing another sheet. I want to find the number of webinars we have published in 2023:

    =COUNTIFS({Editorial Calendar Range 2}, "webinar", {Editorial Calendar Range 3}, "completed", AND({Editorial Calendar Range 1}@cell<= DATE(2023,01,01)))


    Range 1 is End Date

    Range 2 is the Content type (multi-select)

    Range 3 Is the Status

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @JessicaDiPietro Try this:

    =COUNTIFS({Editorial Calendar Range 2}, @cell = "webinar", {Editorial Calendar Range 3}, @cell = "completed", {Editorial Calendar Range 1}, IFERROR(YEAR(@cell), 0) = 2023)

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • It worked, Paul! You have made my day, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!