Countif formula

Options

I would like to count the number of children cells that contain dates in February or March 2016. How do a formalize the criterion to select cells with dates in February 2016 and March 2016?


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try something like this...


    To account for a specific month and year, we need an AND statement

    AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2016)

    AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2016)


    Once we establish that, we can use an OR statement to pull them together...

    OR(AND(................), AND(...............))

    OR(AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2016), AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2016))


    Drop that in as the criteria for your COUNTIFS, and you should be good to go.

    =COUNTIFS(CHILDREN(), OR(AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2016), AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2016)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!