Getting #invalid data type error for this formula

Options

=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", YTD:YTD, YEAR(@cell) = 2021)

Best Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @keesuri25

    Your formula is working for me:

    What type of column is YTD on your sheet? It needs to be a Date type column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like you have a minor syntax issue. Whenever you reference a column name that has a space, number, and/or special character in it, you have to wrap the column name in square brackets whereas you wrapped the entire reference in square brackets. Try this:

    =COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date]:[PoC End date], YEAR(@cell) = 2022)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Double check that the[PoC End date] is in fact set as a date type column. If it is then I believe the issue is because you most likely have some blanks in the column which throws an error on the YEAR function (no date to pull a year from). In that case you will need to modify your YEAR criteria and add in an IFERROR.

    =COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date]:[PoC End date], IFERROR(YEAR(@cell), 0) = 2022)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!