Count values between dates dependent on another column value

SummerEdwards
SummerEdwards ✭✭
edited 12/09/19 in Formulas and Functions

How do I write a COUNTIFs statement to say if value in column A = "City", then count the dates between 1/1/2016 and 12/31/2016?  

Thanks!

Tags:

Comments

  • After working on it a bit longer I figured it out:

    =COUNTIFS({Community}, "Missoula", {Date}, >=DATE(2016, 1, 1), {Date}, <=DATE(2016, 12, 31))

    laugh

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are looking at the entire year, you could save yourself a few keystrokes and use something along the lines of...

     

    =COUNTIFS({Community}, "Missoula", {Date}, YEAR(@cell) = 2016)

    ^^ looks at all of 2016

     

    You can also use this to look at specific months or even a specific month within a specific year.

    =COUNTIFS({Community}, "Missoula", {Date}, MONTH(@cell) = 6)

    ^^ looks at every date that falls within the month of June

    =COUNTIFS({Community}, "Missoula", {Date}, AND(YEAR(@cell) = 2016, MONTH(@cell) = 6)

    ^^ Looks at every date that falls in June of 2016

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!