Multiple Functions IF, AND, COUNTIF, MATCH
 

I have a spreadsheet that looks like this:


Employee Region FLSA Total Allocation
23585 Ops 1 EX 47,912.43
67537 Ops 1 NEX 29,337.12 7,985.40
71448 Ops 1 NEX 45,568.68 7,985.40
58689 Ops 1 NEX 38,535.10 7,985.40
66505 Ops 1 NEX 41,412.41 7,985.40
71423 Ops 1 NEX 33,047.19 7,985.40
70861 Ops 1 NEX 31,366.06 7,985.40
67536 Ops 2 EX 47,589.56
65327 Ops 2 NEX 37,237.89 15,863.19
59857 Ops 2 NEX 40,924.85 15,863.19
59855 Ops 2 NEX 35,523.10 15,863.19
28722 Ops 3 EX 50,807.05
33181 Ops 3 NEX 45,633.28 8,467.84
29564 Ops 3 NEX 45,510.20 8,467.84
70598 Ops 3 NEX 42,090.49 8,467.84
70838 Ops 3 NEX 42,090.49 8,467.84
20274 Ops 3 NEX 45,568.68 8,467.84
28779 Ops 3 NEX 42,755.97 8,467.84

I am trying to write a formula that will allocate a manager's salary to the
other members of his team.

What I want to have happen is if the under the Column called FLSA, the
statues is EX for an employee, then I want that employee's salary costs
listed under the column called Total allocated to all of the other employees
who share the same Region as the employee whose salary is being allocated. I
have keyed in the resutls that I should get under the column called
Allocation but I acn't seem to get a formula that will work.

I believe that the formula should contain IF AND and possibly MATCH
functions but I can't seem to get it to work.

Any thoughts?

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


"Mike" wrote:

I have a spreadsheet that looks like this:


Employee Region FLSA Total Allocation
23585 Ops 1 EX 47,912.43
67537 Ops 1 NEX 29,337.12 7,985.40
71448 Ops 1 NEX 45,568.68 7,985.40
58689 Ops 1 NEX 38,535.10 7,985.40
66505 Ops 1 NEX 41,412.41 7,985.40
71423 Ops 1 NEX 33,047.19 7,985.40
70861 Ops 1 NEX 31,366.06 7,985.40
67536 Ops 2 EX 47,589.56
65327 Ops 2 NEX 37,237.89 15,863.19
59857 Ops 2 NEX 40,924.85 15,863.19
59855 Ops 2 NEX 35,523.10 15,863.19
28722 Ops 3 EX 50,807.05
33181 Ops 3 NEX 45,633.28 8,467.84
29564 Ops 3 NEX 45,510.20 8,467.84
70598 Ops 3 NEX 42,090.49 8,467.84
70838 Ops 3 NEX 42,090.49 8,467.84
20274 Ops 3 NEX 45,568.68 8,467.84
28779 Ops 3 NEX 42,755.97 8,467.84

I am trying to write a formula that will allocate a manager's salary to the
other members of his team.

What I want to have happen is if the under the Column called FLSA, the
statues is EX for an employee, then I want that employee's salary costs
listed under the column called Total allocated to all of the other employees
who share the same Region as the employee whose salary is being allocated. I
have keyed in the resutls that I should get under the column called
Allocation but I acn't seem to get a formula that will work.

I believe that the formula should contain IF AND and possibly MATCH
functions but I can't seem to get it to work.

Any thoughts?

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


"Mike" wrote:

I have a spreadsheet that looks like this:


Employee Region FLSA Total Allocation
23585 Ops 1 EX 47,912.43
67537 Ops 1 NEX 29,337.12 7,985.40
71448 Ops 1 NEX 45,568.68 7,985.40
58689 Ops 1 NEX 38,535.10 7,985.40
66505 Ops 1 NEX 41,412.41 7,985.40
71423 Ops 1 NEX 33,047.19 7,985.40
70861 Ops 1 NEX 31,366.06 7,985.40
67536 Ops 2 EX 47,589.56
65327 Ops 2 NEX 37,237.89 15,863.19
59857 Ops 2 NEX 40,924.85 15,863.19
59855 Ops 2 NEX 35,523.10 15,863.19
28722 Ops 3 EX 50,807.05
33181 Ops 3 NEX 45,633.28 8,467.84
29564 Ops 3 NEX 45,510.20 8,467.84
70598 Ops 3 NEX 42,090.49 8,467.84
70838 Ops 3 NEX 42,090.49 8,467.84
20274 Ops 3 NEX 45,568.68 8,467.84
28779 Ops 3 NEX 42,755.97 8,467.84

I am trying to write a formula that will allocate a manager's salary to the
other members of his team.

What I want to have happen is if the under the Column called FLSA, the
statues is EX for an employee, then I want that employee's salary costs
listed under the column called Total allocated to all of the other employees
who share the same Region as the employee whose salary is being allocated. I
have keyed in the resutls that I should get under the column called
Allocation but I acn't seem to get a formula that will work.

I believe that the formula should contain IF AND and possibly MATCH
functions but I can't seem to get it to work.

Any thoughts?

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