Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
#INVALID DATA TYPE formula Return
The first formula below is returning a #INVALID DATA TYPE when I enter it. But in the second formula the equation works and I am not sure why the first one is not. What am I doing wrong with the Fiscal year part?
does not work:
=SUMIFS([Total With Service Charge (18%) if Applicable]:[Total With Service Charge (18%) if Applicable], [Batch Billing Account]:[Batch Billing Account], =CHRTR19, [Manager Approval]:[Manager Approval], =1, [Internal Processing Status]:[Internal Processing Status], ="Service Complete", [Pick-up Date]:[Pick-up Date], MONTH(@cell) = 10,[Fiscal Year]:[Fiscal Year],=Month19)
Successfully provides a sum:
=SUMIFS([Total With Service Charge (18%) if Applicable]:[Total With Service Charge (18%) if Applicable], [Batch Billing Account]:[Batch Billing Account], =CHRTR19, [Manager Approval]:[Manager Approval], =1, [Internal Processing Status]:[Internal Processing Status], ="Service Complete", [Pick-up Date]:[Pick-up Date], MONTH(@cell) = 10)
Comments
-
it sounds like the [Fiscal Year] and [Month] columns aren't the same data type
are they both text/number?
-
The Pick up Date column is a date column, and both the Fiscal Year and Month columns are text/number columns. And the Values are exactly the same so I am unsure why it is not working.
-
MONTH(@cell) will throw an error if it is blank.
Wrap MONTH(@cell) with IFERROR()
=SUMIFS([Total With Service Charge (18%) if Applicable]:[Total With Service Charge (18%) if Applicable], [Batch Billing Account]:[Batch Billing Account], =CHRTR19, [Manager Approval]:[Manager Approval], =1, [Internal Processing Status]:[Internal Processing Status], ="Service Complete", [Pick-up Date]:[Pick-up Date], IFERROR(MONTH(@cell),0) = 10)
Craig
-
I'm trying to use the following formula to count how many projects I have listed with the due date of Q1/18, Q2/18 and so on.. =COUNTIFS([Strategic Portfolio]:[Strategic Portfolio], "Yes", [Quarter (Planned Finish Date)]:[Quarter (Planned Finish Date)], "Q1/18") but I get #INVALID DATA TYPE as a result.
Both [Quarter (Planned Finish Date)] and [Number of Projects] are Text/Number columns.
The [Quarter (Planned Finish Date)] cell is calculated based on my [Planned Finish Date] date column and I'm using this formula: =IF(Status25 = "Cancelled", "", ("Q" + INT((MONTH([Planned Finish Date]25) / 4) + 1) + "/" + RIGHT(YEAR([Planned Finish Date]25), 2)))
The [Quarter (Planned Finish Date)] column returns the values I want (Q1/18, Q2/18, Q3/18, etc) properly.
Can anyone help me understand why it's not working?
-
You wrote:
Both [Quarter (Planned Finish Date)] and [Number of Projects] are Text/Number columns.
What about [Strategic Portfolio]?
If [Strategic Portfolio], [Quarter (Planned Finish Date)] , and the column containing the formula are all Text/Number, your formula should work.
One of those three are not Text/Number or you will get #INVALID DATA TYPE
Craig
