#NUM! when using column reference in formulae

[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
;-)

 

[Ken Wright]

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

 

[Frank Kabel]

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