COUNTIF within a specific month
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
Best Answer
-
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.
Answers
-
-
#unparseable
-
It looks like there may be a space between your DATE functions (both of them) and the opening parenthesis.
DATE (
vs
DATE(
-
I'm getting #dateexpected now
-
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.
-
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.
-
That same column - MRCU Admission Date
-
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.
-
I'm getting an invalid operation error now:
-
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.
-
Perfect! Thanks, Paul.
-
@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.
-
@kgerholdt Try removing the parenthesis from around ("No") and removing the 2 after Range 5.
Help Article Resources
Categories
Check out the Formula Handbook template!