CountIf with Date range



  • Matt Fruitman
    edited 05/11/21

    @Paul Newcome possible to give this a gander as well?

    =COUNTIFS(Status:Status, NOT(@cell = "Canceled"), AND([Event Begins]:[Event Begins] >= DATE(2020, 1, 1), [Event Begins]:[Event Begins] <= DATE(2020, 1, 31)))

    Thanks so much in advance for your help with this!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Matt Fruitman

    =COUNTIFS(Status:Status, NOT(@cell = "Canceled"), [Event Begins]:[Event Begins], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 1, 31)))

    The AND goes around the criteria portion and uses @cell references.

  • Megan McLehany
    edited 09/16/21


    I tried to use the formula above and I am obviously doing something wrong. I am trying to count the number of rows that have a created date between July 1, and September 30.. so I can get a total number of submissions for Q3. The range is just a single column and the only criteria is the date range...HALP!

    =COUNTIF({2020 Bynder Data Range 3}, AND(@cell >= DATE(2020, 07, 01), @cell <= (2020, 09, 31))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Megan McLehany You forgot the second DATE function.

  • Trying to count how many people were promoted to the role "Professor Emertius" from date range 1/1/20-12/31/21 and I know my formula must be wrong

    =COUNTIFS([Current Rank]:[Current Rank], CONTAINS("Professor Emeritus", (@cell >+DATE(2019,12,31),[Professor Emeritus]:[Professor Emeritus] )

    Error Code= Unparseable

  • @hollyconradsmith you were SO helpful to me earlier this week, any idea of how i can address my formula above?

  • Hi @Paul Newcome,

    I've been following this thread trying to figure out my issue on my own, with no luck. I am trying to count if a dropdown selection has been made in a column on another sheet for only a specific month and year. I want to have a dashboard widget metric that shows volume by the month/year. Here's what I've got:

    =COUNTIFS{Data Range 1}, "PR",AND({Data Range 6},IFERROR(MONTH(@cell) = 10), {Data Range 6}, IFERROR(YEAR(@cell) = 2021)))

    My hope is that it would count number of times PR was selected when date column cell (data Range 6) was October 2021 (not sure if my date format being MM/DD/YY is an issue).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Akellu It should just be a little bit of a syntax error. Try this one...

    =COUNTIFS{Data Range 1}, "PR", {Data Range 6}, AND(IFERROR(MONTH(@cell), 0) = 10, IFERROR(YEAR(@cell), 0) = 2021))

  • @Paul Newcome Thanks so much for your help. I copied your formula and received the following:

  • Beingchirag
    edited 02/01/22

    =COUNTIFS({Improved NL SC Central tracker-22 Range 1}, "Site Nomination", {Improved NL SC Central tracker-22 Range 3}, "Complete", {Improved NL SC Central tracker-22 Range 10}, AND (@cell >= DATE(2022, 2, 1), @cell <= DATE(2022, 2, 7)))

    What wrong in my formula. why i am not getting the result. i want to count with 2 conditions and weekwise data.

    In some of the comments its @cell what is that? where i need to give refrence

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Beingchirag The only potential issue I see is that you have a space after the AND. Try removing that and see if it works.

  • Kendra P
    Kendra P ✭✭✭

    @Paul Newcome

    I am having some issues with pulling a number for my date due. I am trying to see how many would be due in Aug, Sept... Here is the formula I am using.

    =COUNTIF([Date Due]:[Date Due], (> = Date(2022, 08, 01), < = DATE(2022, 08, 31)))

    It is still pulling a zero. Since this is linked to my master page I need to reference the whole column for it to update properly. Your assistance would be greatly appreciated.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Kendra P You would need to use an AND function. I also suggest referencing a month and year number as opposed to two separate dates (in my opinion it is easier to maintain/update).

    =COUNTIFS([Date Due]:[Date Due], AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022))

  • Kendra P
    Kendra P ✭✭✭

    @Paul Newcome Thanks so much! Your the best!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!