COUNTIF header row cell Length is less than a value and range might contain #N/A

Question

In Column A I want to count all the cells in range B2:J2 that their header row cell LEN is less than 17, but one of the cells in my range B2:J2 might be #N/A.

In the example I shared only the cells that are highlighted in grey are relevant, and the formula should return 2.

I would love to get your help.

Answer 1

Using SUMPRODUCT:

=SUMPRODUCT((LEN(B1:J1)<17)*NOT(ISERROR(B2:J2))*NOT(ISBLANK(B2:J2)))