Don Wiss said:
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:C35000))*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:C35000),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.