COUNTIF within a specific month

Kristina Kazakoff

I'm having trouble with a countifs formula that I tried to find an answer for, but I'm still getting an error.

Any help would be greatly appreciated!

I'm looking to count the number of MRCU admissions within the month of February:

Thanks,

Kristina

Paul Newcome

Move the + "" to outside of the function.

DATE(2020, 2, 29) +"")

should be

DATE(2020, 2, 29)) + ""

If I may make a suggestion as to a rewrite of your formula...

Instead of trying to remember or look at a calendar to find the last day of the month, you can specify to look for month and year. You can also nest your criteria inside of an AND statement so that you only need to enter your range once.

=COUNTIFS([MRCU Admission Date]2:[MRCU Admission Date]20, AND(YEAR(@cell) = 2020, MONTH(@cell) = 2))

Depending on your setup there may be even more ways to further increase the flexibility of your formula if you are interested.

Paul Newcome

What error are you receiving exactly?

Kristina Kazakoff

#unparseable

Paul Newcome

It looks like there may be a space between your DATE functions (both of them) and the opening parenthesis.

DATE (

vs

DATE(

Kristina Kazakoff

I'm getting #dateexpected now

Paul Newcome

Double check that your [MRCU Admission Date] column is set to an actual date type column.

If that doesn't do it for you, double check the data in the range to ensure it is actually a date and that error is not in any cell within the range.

Kristina Kazakoff

That column is set as a date type column and there aren't any errors in the range. I'm expecting a single value to returned though, not a date.

Paul Newcome

What column is the formula in?

Kristina Kazakoff

That same column - MRCU Admission Date

Paul Newcome

Ah. That's where the problem is then... You need to convert the end result into text or move it into a text/number column.

=formula + ""

the above (plus open-quote close-quote) will convert it to text so that it can stay in the same column.

Kristina Kazakoff

I'm getting an invalid operation error now:

Paul Newcome

Move the + "" to outside of the function.

DATE(2020, 2, 29) +"")

should be

DATE(2020, 2, 29)) + ""

If I may make a suggestion as to a rewrite of your formula...

Instead of trying to remember or look at a calendar to find the last day of the month, you can specify to look for month and year. You can also nest your criteria inside of an AND statement so that you only need to enter your range once.

=COUNTIFS([MRCU Admission Date]2:[MRCU Admission Date]20, AND(YEAR(@cell) = 2020, MONTH(@cell) = 2))

Depending on your setup there may be even more ways to further increase the flexibility of your formula if you are interested.

Kristina Kazakoff

Perfect! Thanks, Paul.

Paul Newcome

Happy to help! 👍️

kgerholdt

@Paul Newcome I'm new to writing formulas and was hoping you might be able to assist. I'm attempting to count how many violations occur each month, within a category (looking at multiple columns). I got the formula to work and count the "no" answers (=a violation), but I'm struggling to ensure that it only looks at "no" for each specific month.

Paul Newcome

@kgerholdt Try removing the parenthesis from around ("No") and removing the 2 after Range 5.