Just butting in...

Try this formula in another cell:
=SUMPRODUCT(--($A2=$H$2:$H$6),$I$2:$I$6)
(just a smaller range)

Now highlight ($A2=$H$2:$H$6) in the formula bar and hit F9.
You'll see something like:
=SUMPRODUCT(--{FALSE;TRUE;FALSE;TRUE;FALSE},$I$2:$I$6)
Now highlight the last minus through the closing right curly bracket
-{FALSE;TRUE;FALSE;TRUE;FALSE}
and hit F9

You'll see something like:
=SUMPRODUCT(-{0;-1;0;-1;0},$I$2:$I$6)
Now the same for: -{0;-1;0;-1;0}
and hit f9
you'll see:
=SUMPRODUCT({0;1;0;1;0},$I$2:$I$6)

Since =sumproduct() likes to deal with numbers, you had to convert those
true/falses to positive 1's and 0's.

The first unary minus converted true/falses to -1/0's and the second one made
the -1's, +1's.

=====
And if I highlight $i$2:$6 and hit f9, I get:
(with my test data)
=SUMPRODUCT({0;1;0;1;0},{3;3;5;2;0})

The I get 0*3 + 1*3 + 0*5 + 1*2 + 0*0

or just 5.

Remember to hit escape or edit|undo so you don't screw up your real formula when
evaluating this way.

ALEX wrote:

OK, then what does the following do? Can you elaborate?
$A2=$H$2:$H$21

"keepITcool" дÈëÏûÏ¢ÐÂÎÅ
...
-- converts the boolean to number and is called a UNARY minus.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"ALEX" wrote:

In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21)

What does
(--($A2=$H$2:$H$21)

mean???


"orekin"
.com...
I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some
guru feedback on speeding them up, as they are going to be used very
heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248

Thanks In Advance
Orekin




--

Dave Peterson