The boolean statements within some of the parentheses return TRUE/FALSE
values. Prepending -- to them forces those values to be +1 and 0,
respectively. Consequently, multiplying 2 prhases will only return a 1 if
both statements are TRUE:
(A = A) x (B = B) = (TRUE x TRUE) = (1 x 1 )= 1
(A = A) x (B = K) = (TRUE x FALSE) = (1 x 0) = 0

The last minus sign decreases the count of items in the same Region by one.
That's because we want to allocate the manager's expenses amoung the staff
(not including the manager) for that region.

Does that help?
--
Regards,
Ron


"Mike" wrote:

This works like a champ! I did not think of the SUMPRODUCT function. What is
the purpose of the -- and the -1 in the formula?

"Ron Coderre" wrote:

With your sample data in cells A1:E19, try putting this formula in cell F2
and copy it down:
=IF(C2="NEX",SUMPRODUCT(--($B$1:$B$19=B2),--($C$1:$C$19="EX"),$D$1:$D$19)/(COUNTIF($B$1:$B$19,B2)-1),"")

Doest that do what you're asking for?

--
Regards,
Ron