I have a formula that looks as follows:

=IF(AND(H24="NEX",$C24="Operations",U$15="Yes"),SU MPRODUCT(--($D$17:$D$228=D24),--($H$17:$H$228="EX"),$T$17:$T$228)/SUMPRODUCT(--($D$17:$D$225=D24),--($H$17:$H$225="NEX")),"")

What is the prupose of the -- in the formula?

It is called a double unary operator. Basically if coerces Excel to
convert an array of data into a TRUE/FALSE array. This link can give
you a more detailed explanation.

HTH

Steve


http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=504608

- is just the minus sign. The magic is that it forces conversion of an array
of true/false values to numbers, actually -1/0. The second minus sign just
restores the proper sign. So --true is 1; --false is 0. Once you've turned
your arrays from true/false to 1/0, the sumproduct can work as it normally
does on arrays of numbers.

"Mike" wrote:

I have a formula that looks as follows:

=IF(AND(H24="NEX",$C24="Operations",U$15="Yes"),SU MPRODUCT(--($D$17:$D$228=D24),--($H$17:$H$228="EX"),$T$17:$T$228)/SUMPRODUCT(--($D$17:$D$225=D24),--($H$17:$H$225="NEX")),"")

What is the prupose of the -- in the formula?

Thanks for the correction! I meant to use 1/0 not TRUE/FALSE. In any
event, the link still shows a lot of uses of the SUMPRODUCT formula.

Regards,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=504608

Thank you very much.

"Mike" wrote:

I have a formula that looks as follows:

=IF(AND(H24="NEX",$C24="Operations",U$15="Yes"),SU MPRODUCT(--($D$17:$D$228=D24),--($H$17:$H$228="EX"),$T$17:$T$228)/SUMPRODUCT(--($D$17:$D$225=D24),--($H$17:$H$225="NEX")),"")

What is the prupose of the -- in the formula?