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

Hello,

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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)))

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.