# COUNTIFS formula to return # of dates within a specified month

Options

I reeeeeally tried to help myself with this one and I did come across a few other similar posts on month and date range formulas, but I keep getting invalid operation or unparseable errors with everything that I try.

Above is the start of my formula. Range 2 is a date column. How can I return the number of dates that fall within any given month?

I'm looking for a solution that avoids having to put in the first and last dates of each month.

@cell formulas haven't really clicked for me yet ﻿😣

Any input is much appreciated!

• ✭✭✭✭✭✭
Options

Are you looking for just the month, or do you want a specific month within a specific year?

=COUNTIFS({Range 1}, 1, {Range 2}, IFERROR(MONTH(@cell), 0) = ##)

or

=COUNTIFS({Range 1}, 1, {Range 2}, AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####))

• Options

I'm looking for monthly totals, so a specific month within a specific year.

• ✭✭✭✭✭✭
Options

Then you would want to use the second one. All you would have to replace is the ranges with the appropriate ranges you want, "##" with the month number and "####" with the year.

• Options

I keep getting zeros with that. Am I missing something?

• ✭✭✭✭✭✭
Options

Are there any rows that actually contain "2A" AND the date is sometime in March of 2020?

Is it exactly "2A", or is "2A" part of a text string?

Are you referencing a date type column?

• Options

Yes. I tried this with a few different month/year combos and got zero each time.

Range 2 is a date type column.

• ✭✭✭✭✭✭
Options

Ok. Let's try this...

Apply a filter to a sheet that will only show rows that equal "2A" and are between 1 March 2020 and 31 March 2020.

• Options

Soooo I realized where I went wrong. I never actually had formatting that column as a date column -- it only appeared that way. Fixing that made the formula successful.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

Paul,

how do i return the number of workday in a month using only the start date?

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

Try something like this.

```=NETWORKDAYS(Start@row, DATE(YEAR(Start@row), MONTH(Start@row) + 1, 1) - 1)
```

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, Awesome, 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭✭✭✭✭
Options

@Dave Bowie You will need something like this:

=NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row), 1, 1)) - 1)

• ✭✭✭✭✭✭
Options

Thanks! I forgot the IFERROR and the last day of the year.

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!