Countifs formula to count a value within a date range?

Angela Osmar

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

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")