HELP! I'm Lost

[DannyL88]

I have a number of groups of staff, each GROUP has different expectations for the types of work that we do (EVENTS). Within the groups I have staff that work different numbers of days (FTE VALUE). I need a formula that will tell me how many EVENTS are expected for each staff member, based on which GROUP they are in and their FTE VALUE. This will be worked out one row at a time. I have 5 different GROUPS and 3 different FTE VALUES.

Possible combinations each are:

If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 6
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 3
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 3
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 5
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 4
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1

and so on for each row and each event type

Example Mini Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Monthly Typical Expectations (1 FTE)								Expectations
2	Groups	Event type 1	Event type 2	Event type 3		Name	FTE Value	Group	Event Type 1	Event Type 2	Event Type 3
3	IRR	6	2	2		Staff 1
4	IQA	3	4	1		Staff 2
5	ICAO	3	3	1		Staff 3
6	QACAO	2	2	1		Staff 4
7	Secondment	1	0	0		Staff 5
8						Staff 6
9	Monthly Typical Expectations (0.8 FTE)					Staff 7
10	Groups	Event type 1	Event type 2	Event type 3		Staff 8
11	IRR	5	2	2		Staff 9
12	IQA	2	3	1		Staff 10
13	ICAO	2	2	1		Staff 11
14	QACAO	2	2	1		Staff 12
15	Secondment	1	0	0		Staff 13
16						Staff 14
17	Monthly Typical Expectations (0.6 FTE)					Staff 15
18	Groups	Event type 1	Event type 2	Event type 3
19	IRR	4	1	1
20	IQA	2	2	1
21	ICAO	2	2	1
22	QACAO	1	1	1
23	Secondment	1	0	0
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=B3*0.8
B19:D22	B19	=B3*0.6

 

[johnny51981]

Since you are populating both the FTE VALUE and GROUP, you need not go too complicated to get your result. Give this a shot
in Cell I2 to start, and then you can drag across and down as needed

=SUMIF($A$3:$A$7,$H3,B$3:B$7)*$G3

 

[DannyL88]

Absolute Genius, Thank you so much!