COUNTIF dates within the next year.

linda@uidp
edited 12/09/19 in Formulas and Functions

Hi There! 



I'm new to formulas and have tried lots of different ways to solve this, and still haven't managed, so any assistance would be appreciated. I want to count each Event Type which starts within the next 365 days. 



I have managed to count the total number of each Event Type in the column by using:

=COUNTIF([Event Type]:[Event Type], "Networking Reception")

=COUNTIF([Event Type]:[Event Type], "LIstening Session")

but I need to narrow this number down to only those starting within the next year as indicated in the date column. 



I've tried using COUNTIF with the TODAY function, but I'm clearly doing something wrong as I keep getting the dreaded #UNPARSABLE result. 



A screenshot is attached for reference.



Please help! 



Best, 

Linda

 

Screen Shot 2019-10-02 at 2.03.44 PM.png

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Linda,

    Try something like this.

    =COUNTIFS([Event Type]:[Event Type]; "Networking Reception"; Date:Date; >=TODAY(); Date:Date; <=TODAY(365))

    The same version but with the below changes for your and others convenience.

    =COUNTIFS([Event Type]:[Event Type], "Networking Reception", Date:Date, >=TODAY(), Date:Date, <=TODAY(365))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • linda@uidp
    edited 10/06/19

    Hi Andrée, 



    It's still not working. Could there be something wrong in my sheet set up? I'm using a date column for the start date, and I have parent rows for each year. 



    Any futher help would be much appreciate. 

    Best, 

    Linda

     

    Screen Shot 2019-10-03 at 9.30.35 AM.png

    Screen Shot 2019-10-03 at 9.37.09 AM.png

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Andree's solution should work for you. The difference is with the function itself.

     

    COUNTIF only allows one set of range/criteria whereas COUNTIFS (with the "S" on the end) allows multiple sets.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!