COUNTIF or COUNTIFS formula for a date column, criteria based off MONTH and YEAR

Camie Keuck

I am trying to total the number of completed submissions on my Ankeny Repair spreadsheet based off of a specific month from a specific year. Range 4 is the column with the completed dates. My current formula is this...

=COUNTIFS(MONTH({Ankeny Repair Range 4} = 1) AND (YEAR({Ankeny Repair Range 4}, =2020)))

The error message I get is this...

#UNPARSEABLE

Honestly I've tried this formula so many different ways I don't know what is the correct function to use.I do know that I am missing the IFERROR() part as welll since some of the dates are blank within the range.

Any help is very much appreciated - thank you!

Paul Newcome

Try something like this...

=COUNTIFS({Ankeny Repair Range 4}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 6))

Jason Tarpinian

I gave this a shot and can make it work using some dummy columns for the Month and Year (using the simple YEAR() and MONTH() funcitons, maybe somebody could expand the formula knowing how to use the @cell better than me). You can change the 6 and 2020 to a referenced cell, but I think this is what you are looking for. You don't need the AND since the COUNTIFS function assumes there will be multiple criteria.

=COUNTIFS(Month:Month, 6, Year:Year, 2020)

Just played around more with the @cell myself and figured it out:

=COUNTIFS([Completed Dates]:[Completed Dates], YEAR(@cell) = 2020, [Completed Dates]:[Completed Dates], MONTH(@cell) = 6)

Paul Newcome

Try something like this...

=COUNTIFS({Ankeny Repair Range 4}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 6))

Camie Keuck

Thank you for the help, that did the trick! Appreciate the assistance :)

Paul Newcome

Happy to help! 👍️

Elizabeth Jones

I've tried this and am getting "invalid data type. Any idea what might be wrong?

=COUNTIFS([Date Conducted]:[Date Conducted], YEAR(@cell) = 2020, [Date Conducted]:[Date Conducted], MONTH(@cell) = 7)

Paul Newcome

@Elizabeth Jones Make sure that [Date Conducted] is a date type column.

Ross Novotny

Does anyone know why you can't reference an entire column in a CountIf Formula with Dates. So this formula works: =COUNTIF(Date1:Date3, YEAR(@cell) = 2021), but this formula does not work: =COUNTIF(Date:Date, YEAR(@cell) = 2021). I get an "Invalid Data Type"? Seems limited that you would have to have a definite range vs. an entire column.

Kelly Moore

Try

=COUNTIF(Date:Date, IFERROR(YEAR(@cell), 0) = 2021)

You may have blank or other non-date values in your range

Paul Newcome

@Ross Novotny I agree with @KDM. It sounds like you should be able to incorporate an IFERROR with the YEAR function to allow the formula to reference an entire column.

Sue Hill

Trying to accomplish something similar with a cross sheet reference...trying for a few days with variants of the below formula...counting months by year review Status "Not Able To Resolve".

=COUNTIFS({Review Status} "Not Able To Resolve", AND(IFERROR({Year}(@cell), 0) = 2021), IFERROR({Month Number}(@cell), 0) = 4))

Paul Newcome

@Sue Hill Are your year and month in separate columns?

Sue Hill

yes they are

Paul Newcome

@Sue Hill In that case, you are going to want something that looks like this:

=COUNTIFS({Review Status} "Not Able To Resolve", {Year}, @cell = 2021, {Month Number}, @cell = 4)

Sue Hill

Did not work--see formula: =COUNTIFS({Review Status} "Not Able To Resolve", {Year}, @cell = 2021, {Month Number}, @cell = 4)

Paul Newcome

Are you getting an incorrect result or an error?

How are the Year and Month columns populated?