Count months in another sheet but only within a certain year

Nicolette Marinos

I have one sheet with dates listed (ex: 9/10/21) that range from 2020, 2021, and 2022. I'm using this formula to count how many are in each month:

=COUNTIF({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1)

But I'd like to break it down by year so I'd like to find how many are in January of 2021 only.

Thanks!

Andrée Starå

@Nicolette Marinos

Happy to help!

You'd use the COUNTIFS instead of the COUNTIF function.

Try this.

=COUNTIFS({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1), {Head And Neck_Project Overview Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

Did that work?

Andrée Starå

Hi @Nicolette Marinos

I hope you're well and safe!

Try something like this.

=COUNTIFS({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1),
{Head And Neck_Project Overview Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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!

Nicolette Marinos

Hi Andree! Thanks so much!

It's coming up as #UNPARSEABLE

=COUNTIF({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1), {Head And Neck_Project Overview Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

Thoughts?!

Andrée Starå

@Nicolette Marinos

Happy to help!

You'd use the COUNTIFS instead of the COUNTIF function.

Try this.

=COUNTIFS({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1), {Head And Neck_Project Overview Range 1}, IFERROR(YEAR(@cell), 0) = 2021)

Did that work?

MichelleBohn

@Andrée Starå I'm having this same issue and my formula is coming up #UNPARSEABLE too?

=COUNTIFS({ABM Janitorial Request Tracker Range DATE}, IFERROR(MONTH(@cell), 0) = [Primary Column]@row, {ABM Janitorial Request Tracker Range DATE}, IFERROR(YEAR(@cell), 0) = 2021)

Can you see where my error might be? If I do just the Countif for the month's it works but now that we are getting into another FY I need to add the counts for 2020 Nov and 2021 Nov.

Thanks,

Michelle

Andrée Starå

Hi @MichelleBohn

I hope you're well and safe!

Try something like this.

=COUNTIFS({ABM Janitorial Request Tracker Range DATE}, IFERROR(MONTH(@cell), 0) = [Primary Column]@row), {ABM Janitorial Request Tracker Range DATE}, IFERROR(YEAR(@cell), 0) = 2021)

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

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!

MichelleBohn

No that didn't work, but thanks for responding.

Andrée Starå

@MichelleBohn

Happy to help!

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

MichelleBohn

@Andrée Starå - Great news I found my error! -

=COUNTIFS({ABM Janitorial Request Tracker Range DATE}, IFERROR(MONTH(@cell), 0) = [Primary Column]@row)👈️🤦‍♀️, {ABM Janitorial Request Tracker Range DATE}, IFERROR(YEAR(@cell), 0) = 2021)

No parenthesis after =[Primary Column]@row,

This one is working!! - =COUNTIFS({ABM Janitorial Request Tracker Range DATE}, IFERROR(MONTH(@cell), 0) = [Primary Column]@row, {ABM Janitorial Request Tracker Range DATE}, IFERROR(YEAR(@cell), 0) = 2021)

Thanks again for your assistance I'm good to go at the moment!

Andrée Starå

@MichelleBohn

Excellent!

Glad you got it working!

You're more than welcome!

CaseyC

I'm using the same sort of formula counting how many items are in Jan of 2022 and my formula works, what I'm wondering is it somehow possible to not have to update the year? I'll be coping his summary sheet each year and I'm hoping there's a way that each Dec when setting up the new year's sheet I won't need to manually change the 2022 to 2023 (then 2023 to 2024, etc.) several dozen times.

Genevieve P.

Hi @CaseyC

What about referencing a Cell in the formula instead of typing in the number? For example a cell in a Sheet Summary field which identifies the Year.

Then you would update the formula to look there:

=COUNTIFS({Reference}, IFERROR(YEAR(@cell), 0) = [Year Reference]#)

This means you'd only have to update one cell when you copy the sheet and it will update all of your formulas.

Cheers,

Genevieve

CaseyC

Great suggestion @Genevieve P. thanks!