Count months in another sheet but only within a certain year

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!

Tags:

• ✭✭✭✭✭✭

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?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• 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?!

• ✭✭✭✭✭✭

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?

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

@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

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

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

• ✭✭✭✭✭✭

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, [email protected])

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

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

• ✭✭✭✭✭✭

Excellent!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

edited 12/02/22

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

• Great suggestion @Genevieve P. thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!