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

Options
✭✭✭

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!

«1

• ✭✭✭✭✭✭
edited 06/19/20
Options

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)

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Try

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

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

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

yes they are

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Are you getting an incorrect result or an error?

How are the Year and Month columns populated?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!