Need Help with #INVALID DATA TYPE Error for COUNTIFS Formula with Multiple Cross-sheet References



I have this formula:

=COUNTIFS({Certifications Range 2}, CONTAINS("CyberArk", @cell), AND({Certifications Range 1}, CONTAINS("Expiring", @cell), CONTAINS("Current", @cell)))

I keep getting #INVALID DATA TYPE

Range 2 and Range 1 are both referencing Dropdown list columns from another sheet. I know that the problem is occurring between AND and the end, but I don't know what I am doing wrong.



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Kayla Peacock

    You can leave the AND where it is and add the {Certifications Range 1} a second time within the AND in order to complete the proper syntax:

    =COUNTIFS({Certifications Range 2}, CONTAINS("CyberArk", @cell), AND({Certifications Range 1}, CONTAINS("Expiring", @cell), {Certifications Range 1}, CONTAINS("Current", @cell)))

    or you can move the AND to cover the two criteria:

    =COUNTIFS({Certifications Range 2}, CONTAINS("CyberArk", @cell), {Certifications Range 1}, AND(CONTAINS("Expiring", @cell), CONTAINS("Current", @cell)))


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Kayla Peacock

    Neither of these returned the proper results. The first one still produces the INVALID DATA TYPE ERROR and the second one produced actual results, but the number was incorrect. It should have returned 1 based on my sheet's data, but it returned 0.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!