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

06/19/20
Accepted

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!

Best Answer

Previous1

Answers

  • Jason TarpinianJason Tarpinian ✭✭✭✭✭
    edited 06/19/20

    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 Platinum Partner

  • Camie KeuckCamie Keuck ✭✭✭✭✭

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

  • Ross NovotnyRoss 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.

  • KDMKDM ✭✭✭✭✭

    Try

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

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

  • Paul NewcomePaul 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.

  • 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 NewcomePaul Newcome ✭✭✭✭✭

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

  • yes they are

  • Paul NewcomePaul 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)

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are you getting an incorrect result or an error?


    How are the Year and Month columns populated?

Sign In or Register to comment.