"Don Wiss" wrote:
I think the problem has to do with many of the cells in C1:C35000
are non-numeric. I tried this:
{=SUM(AND(LEFT(A1:A35000,3)<"HD:",ISNUMBER(C1:C35 000))*C1:C35000)}
And still n.g.

The following normally-entered formula (just press Enter as usual) should
work:

=SUMPRODUCT(--(LEFT(A1:A35000,3)<"HD:"),C1:C35000)

The double-negate (--) converts TRUE and FALSE to 1 and 0, which SUMPRODUCT
requires to work as intended.

It is not necessary to test ISNUMBER(C1:C35000) because SUMPRODUCT treats
text and logic values in C1:C35000 as if they were zero.

For future purposes, alternatively, the following array-entered formula
(press ctrl+shift+Enter instead of just Enter) should also work:

=SUM(IF(LEFT(A1:A35000,3)<"HD:",IF(ISNUMBER(C1:C3 5000),C1:C35000)))

That works because of the left-to-right processing that the IF
pseudo-function does.

We cannot use AND(LEFT(A1:A35000,3)<"HD:",ISNUMBER(C1:C35000) in array
formulas because the AND function itself processes the entire arguments,
instead of processing them row by row as intended.