Large criterion name

Neville

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?

builder.png

Kelly Moore

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

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

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

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

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