problem with array formula

  • Thread starter Thread starter Don Wiss
  • Start date Start date
D

Don Wiss

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).
 
hi Don,

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

isabelle

Le 2014-03-10 21:16, Don Wiss a écrit :
 
Le 2014-03-10 21:16, Don Wiss a écrit :
Click to expand...
=SUMPRODUCT((LEFT(A1:A35000,3)<>"HD:")*(C1:C35000))
Click to expand...

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:C35000))*C1:C35000)}

And still n.g.

Don. www.donwiss.com (e-mail link at home page bottom).
 
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.
Click to expand...

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.
 
The following normally-entered formula (just press Enter as usual) should
work:

=SUMPRODUCT(--(LEFT(A1:A35000,3)<>"HD:"),C1:C35000)
Click to expand...
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)))
Click to expand...

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:C35000),1)))}

Don. www.donwiss.com (e-mail link at home page bottom).
 
Don Wiss said:
joeu2004 said:
=SUMPRODUCT(--(LEFT(A1:A35000,3)<>"HD:"),C1:C35000)
Click to expand...
[....]
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:C35000),1)))}
Click to expand...

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.
 
Back
Top