RE: Multiple Functions IF, AND, COUNTIF, MATCH

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

.

Relevant Pages