Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Imda14u wrote:
Dave Peterson schreef:
=sumproduct(--(a1:a10="john"),--isnumber(b1:b10))
It works great, what I don't understand are the double dashes.
I've tried without them and the formula fails.
Also, when instead of text "john" you refer to a cell with the text
"john" it seems that you must confirm the formula with ctrl+shift+enter.
When you fill the formula down to refer to another cell with name "dave"
you get the same result as for "john", not so however when confirmed
as array.
puzzles and questions....
greets,
Sybolt
--
Dave Peterson