COUNTIFS formula to return # of dates within a specified month

Kristina Kazakoff

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!

Paul Newcome

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) = ####))

Kristina Kazakoff

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

Paul Newcome

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.

Kristina Kazakoff

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

Paul Newcome

Double check your data.

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?

Kristina Kazakoff

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

Range 2 is a date type column.

Paul Newcome

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.

Kristina Kazakoff

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.

Thanks for your help!

Paul Newcome

Happy to help! 👍️

Dave Bowie

Paul,

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

Andrée Starå

Hi @Dave Bowie

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!

Paul Newcome

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

Andrée Starå

@Paul Newcome

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