#NUM! when using column reference in formulae

  • Thread starter Thread starter Loomah
  • Start date Start date
L

Loomah

Hi All

Two issues regarding the formula below, mainly coz "I need to understand!"

=SUMPRODUCT(N(A1:A99="D"),N(B1:B99=1))

1) How come I get #NUM! if I change the range to include the whole column
i.e. =SUMPRODUCT(N(A:A="D"),N(B:B=1)) produces the error
and yet with other formulae it's fine using the whole column,
=countif(b:b,1) works ok!

2) Perhaps not as important, is there any reason why I had to evaluate
true/false to 1/0 to get this formula to work
i.e. why did I need to use N(A1:A99="D")?

Any help greatly appreciated
;-)
 
Hi Loomah - nice to see you over this side of the fence ;-)

1) Whilst it is not entered as an array formula, SUMPRODUCT has practically all
the characteristics of an array formula, one of which being that you cannot use
full column or row references in there. You must set a range, or use a dynamic
range.

2) You didn't, as you could have simply used:-

=SUMPRODUCT((A1:A99="D")*(B1:B99=1))
 
Hi
1. SUMPRODUCT does not accept references like B:B. You have to enter
them like B1:B20000
2. You have to use a mathematical operation to convert a boolean value
to a number. Instead of N() you may use
=--(A1:A99="D")
or
=1*(A1:A99="D")
 
Ken, Thanks for the reply.
My secret's out! This is where I usually come for answers I can't get on TT
and try to give some while I'm here. Not always successful!!

Obviously you've seen the TT thread I was trying to get to the bottom of!!

However, re my point 2 I did need to evaluate the logical tests (I shouldn't
have but I did) to get it to work at all on xl2k. Do I have a problem?
;-)
 
:-)

Nope. Frank has also answered that one. The reason you had to do it was that
you used the SUMPRODUCT formula in the format of

=SUMPRODUCT( COND1 , COND2 )

and therefore needed to do the equivalent of the mathematical operation Frank
referred to, to make each of COND1 COND2 boolean.

In the example I gave

=SUMPRODUCT( COND1 * COND2 )

the mathematical operation is done between the two conditions with the * and so
you don't need to any further work to create the boolean values.
 
Back
Top