Count dates within a specific range

05/04/21
Answered - Pending Review

I am collecting performance data for my project. I have a report that contains all of the data I want to analyze and report statistics on. I'd like to look through a column containing dates including some empty cells, and count how many cells contain dates within a specific range. I'd like to count cells containing dates from February 2021 for example. I attempted using countifs and just a simple if, as well as a not(isblank()) but I keep getting the unparseable error.


Please help

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Is the reference data on an actual report or on a sheet separate from where you want to display these metrics?

  • The reference data is in a report, but all of the data in the report comes from a few different sheets, so I can technically reference sheets to perform calculations as well. I am attempting to make all these calculations in a separate sheet.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. Cross sheet formulas cannot reference a report. So we will have to figure out a way to reference the sheets themselves.


    Are all of the sheets set up the same (as if from a template), and are you pulling the same data from all of the sheets?

  • Yes all of the sheets containing data appear to be created from a template and have the same format. The date I'm looking for is in the same column for the couple of sheets but like I said the column contains blank cells as well.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. There are a few ways to do this. The easiest to set up (but probably the worst for growth) would be to use three separate COUNTIFS and add them together.

    The example below would be for February 2021.

    =COUNTIFS({First Sheet Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 2)) + COUNTIFS({Second Sheet Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 2)) + COUNTIFS({Second Sheet Date Column}, AND(IFERROR(YEAR(@cell), 0) = 2021, IFERROR(MONTH(@cell), 0) = 2))

Sign In or Register to comment.