COUNTIFS giving INVALID DATA TYPE

Options

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!

Tags:

Best Answer

  • Alex.Dixon
    Alex.Dixon ✭✭
    Answer ✓
    Options

    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

Answers

  • Alex.Dixon
    Alex.Dixon ✭✭
    Answer ✓
    Options

    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

  • Dakota Haeffner
    Dakota Haeffner ✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!