problem with array formula
 

It has been years since I've done an array formula. And I was never that
good with them. I would think that this should work:

{=SUM((LEFT(A1:A35000,3)<"HD:")*(C1:C35000))}

It doesn't. It gets #VALUE!

Don. www.donwiss.com (e-mail link at home page bottom).

problem with array formula
 

hi Don,

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

isabelle

Le 2014-03-10 21:16, Don Wiss a écrit :
It has been years since I've done an array formula. And I was never that
good with them. I would think that this should work:

{=SUM((LEFT(A1:A35000,3)<"HD:")*(C1:C35000))}

It doesn't. It gets #VALUE!

Don. www.donwiss.com (e-mail link at home page bottom).

problem with array formula
 

On Mon, 10 Mar 2014 21:36:29 -0400, isabelle wrote:
Le 2014-03-10 21:16, Don Wiss a écrit :
It has been years since I've done an array formula. And I was never that
good with them. I would think that this should work:

{=SUM((LEFT(A1:A35000,3)<"HD:")*(C1:C35000))}

It doesn't. It gets #VALUE!

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

Nope.

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.

Don. www.donwiss.com (e-mail link at home page bottom).

problem with array formula
 

"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.

problem with array formula
 

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).

problem with array formula
 

"Don Wiss" wrote:
"joeu2004" wrote:
=SUMPRODUCT(--(LEFT(A1:A35000,3)<"HD:"),C1:C35000)
[....]
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)))}

Alternatively, the following normally-entered formula (just press Enter as
usual):

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

Previously, I noted the use of double-negate (--) to convert TRUE and FALSE
into 1 and 0. I should have noted that any arithmetic operation
accomplishes the same thing.

That is why it is sufficient to just multiply the two logic results; no need
to also double-negate.

I avoid array-entered formulas whenever reasonable because they are tedious
to use and error-prone. We must always remember to press ctrl+shift+Enter
after editing. If we forget and press just Enter instead, the formula does
__not__ return an error in some contexts, leading to incorrect results.