Countifs by year and month with nested countifs for other values

abargholz

I am trying to count up survey responses by month and year on another sheet. I took this in bite size pieces so this first formula is the whole formula currently working on.

=(COUNTIFS({Survey Range 2},YEAR{Survey Range 2}=2020,MONTH{Survey Range 2}=9),((COUNTIFS({Product}, "Buildings", {Survey Range 1}, "4-Meets Needs")) + (COUNTIFS({Product}, "Buildings", {Survey Range 1}, "5-Exceeds Needs"))) / COUNTIF({Product}, "Buildings"))

This is the portion that isn't working =(COUNTIFS({Survey Range 2},YEAR{Survey Range 2}=2020,MONTH{Survey Range 2}=9),

This portion is working as expected ((COUNTIFS({Product}, "Buildings", {Survey Range 1}, "4-Meets Needs")) + (COUNTIFS({Product}, "Buildings", {Survey Range 1}, "5-Exceeds Needs"))) / COUNTIF({Product}, "Buildings"))

Currently returning #Unparseable

The Survey Range 2 is referencing a date-formated column in another sheet. (image below)

Athar

@abargholz create two columns "Year" and "Month" next to this auto date column.

Put column formula YEAR ([Created]@row) and MONTH([Created]@row) in above columns. Then reference these columns in your formula as per your requirement. I hope it'll solve your problem.

_____________________________________________________________________________________________________________________

datalime.net

abargholz

After re-reading this, I hope it makes sense. Clearly, I didn't articulate all of that cohesively.

The whole formula is returning #Unparseable not that last portion

Athar

@abargholz create two columns "Year" and "Month" next to this auto date column.

Put column formula YEAR ([Created]@row) and MONTH([Created]@row) in above columns. Then reference these columns in your formula as per your requirement. I hope it'll solve your problem.

_____________________________________________________________________________________________________________________

datalime.net

abargholz

Thank you very much, Athar! 🙌

Athar

You're welcome @abargholz

lauren12345

Is there a way to do this without creating additional columns?

I am having a similar issue and would like to use one formula to calculate =COUNTIF(YEAR({Date from other sheet}), 2023). When I test the year formula on an individual cell, it returns the year, but when I embed it in COUNTIF(), it does not work. The "date from other sheet" column is formatted properly. Creating additional columns makes our sheets very messy.

DKazatsky

@lauren12345

You can use something like this: =COUNTIFS({Date Range}, IFERROR(YEAR(@cell), 0) = 2023, {Date Range}, IFERROR(MONTH(@cell), 0) = 2)

Hope this helps,

Dave