Countifs formula to count a value within a date range?


Our company has ongoing surveys and the raw data is organized by different dates (dates the survey was complete). We want to only count the cells for a particular date range in our calculations sheet to give us the survey results for that time frame.

For example: I only want smartsheet to look at the surveys completed between 1/1/2021 and 12/31/2021 and within that, only look at the values that equal "5" for a particular question (column).

Would this be a countifs formula? If so, how would it be structured? The goal is some sort of automation as more surveys are completed.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You have a few different options. If you want specific dates:

    =COUNTIFS([Date Column]:[Date Column], AND(@cell>= DATE(2021, 01, 01), @cell<= DATE(2021,12,31)), Question:Question, @cell = "5")

    If you just want a year:

    =COUNTIFS([Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2021, Question:Question, @cell = "5")

    If you want a specific year and month (January in this example):

    =COUNTIFS([Date Column]:[Date Column], AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 1), Question:Question, @cell = "5")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!