Large criterion name

Hello,

I'm trying to make a formula to count the number of jobs by builder for the month of January to go into my sheet summary.

I managed to get the formula working for January however when i copied and pasted the formula and changed the name of the builder i got #invalid operation.

The working formula is as follows,

=COUNTIFS(Customer:Customer, "Burbank", [Delivery Date]:[Delivery Date], IFERROR(MONTH(@cell) = 1, =0))

The invalid formula is,

=COUNTIFS(Customer:Customer, "HICKINBOTHAM", [Delivery Date]:[Delivery Date], IFERROR(MONTH(@cell) = 1, =0))

I tested a theory that the name "hickinbotham" was too long and it seems to be right as the formula works builders with smaller names.

the formula works with 11 characters but not 12 characters (as i tried "hickinbotha" and it worked).

Is there anything I can do without having to change the name of the builder?


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Neville

    I have an idea - a trick I use that often works. Although it doesn't make sense, it is important that the ISDATE criteria is first in the COUNTIFS.

    =COUNTIFS([Delivery Date]:[Delivery Date], ISDATE(@cell), Customer:Customer, "HICKINBOTHAM", [Delivery Date]:[Delivery Date], IFERROR(MONTH(@cell) = 1, 0))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Neville

    The length of your text string is not the source of your error. To continue troubleshooting, just as a test, delete the date range, and criteria from your COUNTIFS formula. My guess is that you have an error within your date range that is associated with your HICKINBOTHAM customer.

    Let me know

    Kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Neville

    I have an idea - a trick I use that often works. Although it doesn't make sense, it is important that the ISDATE criteria is first in the COUNTIFS.

    =COUNTIFS([Delivery Date]:[Delivery Date], ISDATE(@cell), Customer:Customer, "HICKINBOTHAM", [Delivery Date]:[Delivery Date], IFERROR(MONTH(@cell) = 1, 0))

  • Neville
    Neville ✭✭
    edited 01/04/22

    Hi @Kelly Moore,

    Thank you so much! it worked perfectly.

    That is strange for the ISDATE to be needed on one formula but not the other, as long as it works though I'm happy,

    Thanks again :)

    Regards,

    Neville

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Neville

    Because the above formula works, this validates my original theory that you have a date, associated with this customer's name, that has an error in it. This would explain why the ISDATE is needed now and not originally. To help my own data prevent these kind of errors I often throw the ISDATE criteria in my formulas.

    Glad it worked for your case

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!