# Large criterion name

Options
✭✭

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
edited 01/04/22
Options

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

• ✭✭✭✭✭✭
Options

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.