SUMPRODUCT OR OTHER FORMULA QUESTION

[graemekoz]

Good afternoon

I'm hoping someone may be able to help with advice if something is even possible.

I need to create a staffing roster in excel which changes the rates based on specific days of the week, times and the position.

I've created a table (part of this below) which shows the various positions and they are broken down by day of the week (1 - 7 and then 8 is a public holiday).

In the roster itself each position has a category (event Staff, event supervisor etc). The Day of the week is also calculated by a function.

I'm trying to work out a formula which basically does the following:

1. What is the position (eg event staff, supervisor). THere are about 7 different positions
2. Once it knows the position, it looks at the day of the week.
3. Once it has the position and the day of the week, in the RATE cell it inputs the rate which satisfies both conditions.

Am i asking too much for Excel to do this or is there a specific function which can achieve this. I tried Sumproduct but i can't seem to get it to work and give me the correct rate as it isn't finding values as such within those two columns. I've also thought about VLOOKUP but trying to work out how to get it to satisfy both requirements before giving a value.

Can anyone help?

		DAY	AM	PM
Event Staff	Admin, Events & Comms	1​	$1.00​	$2.00​
Event Staff	Admin, Events & Comms	2​	$1.00​	$2.00​
Event Staff	Admin, Events & Comms	3​	$1.00​	$2.00​
Event Staff	Admin, Events & Comms	4​	$1.00​	$2.00​
Event Staff	Admin, Events & Comms	5​	$1.00​	$2.00​
Event Staff	Admin, Events & Comms	6​	$3.00​	$6.00​
Event Staff	Admin, Events & Comms	7​	$5.00​	$10.00​
Event Staff	Admin, Events & Comms	8​	$6.00​	$12.00​
Event Supervisor	Supervisor Events/Comms	1​	$4.00​	$8.00​
Event Supervisor	Supervisor Events/Comms	2​	$4.00​	$8.00​
Event Supervisor	Supervisor Events/Comms	3​	$4.00​	$8.00​
Event Supervisor	Supervisor Events/Comms	4​	$4.00​	$8.00​
Event Supervisor	Supervisor Events/Comms	5​	$4.00​	$8.00​
Event Supervisor	Supervisor Events/Comms	6​	$6.00​	$12.00​
Event Supervisor	Supervisor Events/Comms	7	$8.00	$16.00
Event Supervisor	Supervisor Events/Comms	8	$10.00	$20.00

 

[Alex Blakenburg]

If you can update your profile to show what version of excel you are using, we will be better able to work out what formula options you have available to you.

Nothing you have said so far is particularly difficult. Without getting into the complexities of AM & PM, here is an example of how it could work, using just the SumIfs formula.

20210401 Multi Dimensional Simple Lookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Lookup Table							RESULTS of Lookup
2								Criteria		Results
3
4	Position	Dept	Day of week	AM	PM			Position	Day of week	AM	PM
5	Event Staff	Admin, Events & Comms	1	$ 1.00	$ 2.00			Event Staff	7	$ 5.00	$ 10.00
6	Event Staff	Admin, Events & Comms	2	$ 1.00	$ 2.00
7	Event Staff	Admin, Events & Comms	3	$ 1.00	$ 2.00
8	Event Staff	Admin, Events & Comms	4	$ 1.00	$ 2.00
9	Event Staff	Admin, Events & Comms	5	$ 1.00	$ 2.00
10	Event Staff	Admin, Events & Comms	6	$ 3.00	$ 6.00
11	Event Staff	Admin, Events & Comms	7	$ 5.00	$ 10.00
12	Event Staff	Admin, Events & Comms	8	$ 6.00	$ 12.00
13	Event Supervisor	Supervisor Events/Comms	1	$ 4.00	$ 8.00
14	Event Supervisor	Supervisor Events/Comms	2	$ 4.00	$ 8.00
15	Event Supervisor	Supervisor Events/Comms	3	$ 4.00	$ 8.00
16	Event Supervisor	Supervisor Events/Comms	4	$ 4.00	$ 8.00
17	Event Supervisor	Supervisor Events/Comms	5	$ 4.00	$ 8.00
18	Event Supervisor	Supervisor Events/Comms	6	$ 6.00	$ 12.00
19	Event Supervisor	Supervisor Events/Comms	7	$ 8.00	$ 16.00
20	Event Supervisor	Supervisor Events/Comms	8	$ 10.00	$ 20.00
21
Data

Cell Formulas
Range	Formula
J5	J5	=SUMIFS($D$5:$D$20,$A$5:$A$20,$H5,$C$5:$C$20,$I5)
K5	K5	=SUMIFS($E$5:$E$20,$A$5:$A$20,$H5,$C$5:$C$20,$I5)