Problem with INVALID DATA TYPE error with COUNTIF formula

Hello,

I am trying to capture all entries where my delay (calculated using NETDAYS formula) is bigger than 50, using COUNTIF, and getting an INVALID DATA TYPE error. I tried many things, including making sure the dates used to calculate the delay ARE proper dates. I am stuck.

The strangest thing is I used to had this formula working on the same sheet, but I modified it and I can't go back to it working now.

Any hints?

I use a very simple: =COUNIF([Delay]:[Delay] >50)

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Joanna Langwald

    I hope you're well and safe!

    Try something like this.

    =COUNTIF(Delay:Delay, >50)

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • No, sadly it doesn't. I really did try to sort it out myself before I posted my question here ;)

    I tried with square brackets, without, with @row, and without, originally I was using COUNTIFS, as I wanted to capture only Open tickets with the delay higher than 50, then reduced the complexity to find out where the problem is..

    =COUNTIFS(Status:Status, "Open", [Delay]:[Delay], >50)

    Still no luck :(

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 10/04/22

    @Joanna Langwald

    Strange!

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks for trying, Andree ;)

    OK, screens below:

    1) How I capture my delay (there are two columns Delay and Delay 2, as I had to make sure the delay is being calculated based on proper dates (I restricted data type to Dates in Delay 2 column)

    2) How I want to count all instances where delay exceeds 50, and get error message.


  • Right, I thought I will leave a comment to conclude - we've found what was corrupting our calculations! ;) We had some rows populated partially with content on the very bottom of the list, which was causing the delay (NETDAYS) formula to return an error in few rows (as there were no dates to count the delay put in). As the COUNTIF formula was set up to refer to the whole column - it was also taking under consideration the bottom lines, not properly filled in, which was resulting in an error ;)

    When the "extra" content deleted - formula started working ;) Silly mistake, beware of your bottom rows! ;)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!