COUNTIFS giving INVALID DATA TYPE

Hello,

I am working on a Dashboard for a Program Plan and I am trying to get a read for the amount of items in a particular sheet that are past due that are not completed. These columns are identified by "Target End Date" and "Health".

Target End Date is a date column. Health is a single select drop down with the options, 'At Risk,' 'On Track,' 'High Risk,' and 'Complete.'

I used the following formula to count:

=COUNTIFS({Kansas City UHC New Market - Target End Date}, <TODAY(), {Kansas City UHC New Market - Health}, <>"Complete")

This was returning 0 however, when filtered on the sheet I was returning 13. It seems as though the formula is not counting any fields left blank because some task are not labeled as they haven't started yet. This is one of hundreds of sheets so a change to the sheet is next to impossible.

I also tried this formula and it was returning Invalid Data Type:

=COUNTIFS({Kansas City UHC New Market - Target End Date}, <TODAY(), OR({Kansas City UHC New Market - Health}, "", {Kansas City UHC New Market - Health}, <>"Complete"))

Thank you for any help!

Find more posts tagged with

Formulas

Accepted answers

Alex.Dixon

For your first function, I believe changing the second logical statement to OR(@cell<>"Complete",ISBLANK(@cell)) should resolve the issue if all uncounted fields are due to blanks (at least based on my test).

For the second one, you are both missing the "range2" input, and OR requires an entire logical statement as each input.

=COUNTIFS({Kansas City UHC New Market - Target End Date}, <TODAY(), {Kansas City UHC New Market - Health},OR({Kansas City UHC New Market - Health}="", {Kansas City UHC New Market - Health}<>"Complete"))

-Alex

All comments

Alex.Dixon

For the second one, you are both missing the "range2" input, and OR requires an entire logical statement as each input.

-Alex

Dakota Haeffner

@alex.dixon Worked perfectly, I didn't think to you ISBLANK at all thank you! This community is undefeated. 🤘

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.