On Mon, 10 Mar 2014 23:33:50 -0700, "joeu2004" wrote:
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)
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:C 35000),C1:C35000)))
Both work just fine. I used the first one. Thanks.
I would also like to get a count. I used this array formula for that:
{=SUM(IF(LEFT(A1:A35000,3)<"HD:",IF(ISNUMBER(C1:C 35000),1)))}
Don.
www.donwiss.com (e-mail link at home page bottom).