@cell Reference for Whole Column

Mike J

I tried to create a formula with a structure like COUNTIF(column:column, YEAR(@cell)=2021) and was getting an error. I realized that if I changed to COUNTIF(column1:column23, YEAR(@cell)=2021), the formula would work. It seems that the @cell reference does not work when a whole column is selected. Does anyone have a workaround? The sheet will have new information added to it, so I don't want to have to keep increasing the range manually each time. I realize I can just make the column reference many more rows than are already filled in, but that seems like poor practice too.

Thanks.

Andrée Starå

Hi @Mike J

I hope you're well and safe!

Try something like this.

=COUNTIF([Column Name]:[Column Name], IFERROR(YEAR(@cell), 0) = 2021)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Mike J

I hope you're well and safe!

Try something like this.

=COUNTIF([Column Name]:[Column Name], IFERROR(YEAR(@cell), 0) = 2021)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mike J

That worked perfectly. Thank you!

KevAnalyst

Mike,

This worked for me...

=COUNTIFS(Column:Column, >=DATE(2021, 1, 1), Column:Column, <=DATE(2021, 12, 31))

Hope this helps!

Kev

Andrée Starå

@Mike J

Excellent!

You're more than welcome!