Not that I could think of--but that doesn't mean much. Maybe someone else will
jump in with a solution for both the 2 and 3 character strings.

joeu2004 wrote:

On Feb 24, 5:35 pm, Dave Peterson wrote:
=SUMPRODUCT(--(LEFT(A19:A26,2)<"ad"),--(LEFT(A19:A26,3)<"sub"),C19:C26)

That's the best I could come up with, too. I was struggling to see if
there was a way to calculate LEFT() only once. I vaguely remember
seeing formulas of the form "expr<{const1,const2}" meaning either
OR(expr<"const1",expr<"const2") or
AND(expr<"const1",expr<"const2"), I don't remember which. But I
could not get the "expr<{...,...}" form to work as needed. Besides,
in my vague recollection, "const1" and "const2" were numbers, not
strings.

Anyway, is there no way to avoid calculating LEFT() more than once?

(Other than using a UDF.)

--

Dave Peterson