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:
Move the + "" to outside of the function.
DATE(2020, 2, 29) +"")
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.
CERTIFIED SMARTSHEET PLATINUM PARTNER
Efficiency Live: The Ultimate PM Toolkit Workshop
What error are you receiving exactly?
It looks like there may be a space between your DATE functions (both of them) and the opening parenthesis.
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.
What column is the formula in?
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:
Perfect! Thanks, Paul.
Happy to help! 👍️
@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.
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
Hi everyone, I have one of those pesky formulas that is giving me a hard time. This is for a Summary Sheet I'll use to build a dashboard so I'm referencing another sheet with the data. Here's what I am trying to pull...Count if the following is true: 1) Project name is the same as the project in the summary sheet 2) Month…
Hi There I am using the below formula to check a box if a date is one day greater than today. The formula works fine except if the date field is blank, then it is showing up #Invalid Data Type. How can the results show up as blank instead of #Invalid Data Type if there is no date referenced yet? =IF(NETDAYS([Date to…
I have an intake form where we receive campaign budgets. We are trying to track when our campaigns as a whole start spending over our expected Overall Budget. I need a column formula that can calculate the remaining budget when a new campaign is added. Currently, the intake form is set up to have new campaigns be added at…
©2024. All Rights Reserved Smartsheet Inc.