Help with CONTAINS Returning Invalid Data Type

Options

I have the below formula to count if the department contains Facility and the dates are between January 1, 2022 and January 31, 2022. I am receiving the invalid data error. I have validated the Loss Date column is set for dates and the Dept column is set to text/number.

=COUNTIFS(CONTAINS({Dept}, "Facility"), {Loss Date}, >=DATE(2022, 1, 1), {Loss Date}, <DATE(2022, 1, 31))

Regards,

Ron

Best Answer

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Ronald Anderson 

    I hope you are doing well, 

    I have modified the formula, you can try this formula  

    =COUNTIFS({Dept}, CONTAINS("Facility", @cell), {Loss Date}, >=DATE(2022, 1, 1), {Loss Date}, <DATE(2022, 1, 31))  

    I hope it works for you!

    Thank you 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

Answers

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Ronald Anderson 

    I hope you are doing well, 

    I have modified the formula, you can try this formula  

    =COUNTIFS({Dept}, CONTAINS("Facility", @cell), {Loss Date}, >=DATE(2022, 1, 1), {Loss Date}, <DATE(2022, 1, 31))  

    I hope it works for you!

    Thank you 

    Thanks,

    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Ronald Anderson
    Ronald Anderson ✭✭✭✭
    Options

    @Kaveri Vipat Thank you, this works.

    Appreciate the help!

    Regards,

    Ron

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!