#INVALID DATA TYPE formula Return

bhope51

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)

rjudenberg

it sounds like the [Fiscal Year] and [Month] columns aren't the same data type

are they both text/number?

bhope51

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.

J. Craig Williams

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

J. Bulcao

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?

J. Craig Williams

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