Select the newest dates sum corresponding rows up to a cumulative value in a 3rd column

[Speed67]

I have a list of employees and I want to sum rows associate with a few values in those rows based on title and and least senior. Where this get a little more complicated is some employees are Part time (0.4, 0.53, or something less than 1 but larger than 0) and some are full time (1.00) and there are Casuals that are 0.

The calculation needs to add up liability to a cumulative FTE amount such as 7.1 of the lowest employees seniority with a specific job title.

[alansidman]

I don't understand the rationale how the 8 employees were selected. Can you explain your logic. Why 8 and not 5?

[Speed67]

The number is based on a reduction of staffing associated to services. The problem is the models asked by leadership keeps changing on how many to reduce. So tomorrow they may ask what about the impact of 5 or 10 or 22.5 does that answer your question .

[alansidman]

In that case suggest you use the =Rank function to rank by date. Then you can use the sumifs function.

v	K	L	M
1	Payout contracting
2	78817.5	=RANK(E2,$E$2:$E$18,1)	=SUMIFS($K$2:$K$18,$B$2:$B$18,20,$L$2:$L$18,">3")
3	70155	=RANK(E3,$E$2:$E$18,1)
4	72382.5	=RANK(E4,$E$2:$E$18,1)
5	72382.5	=RANK(E5,$E$2:$E$18,1)
6	87952.5	=RANK(E6,$E$2:$E$18,1)
7	15414.21	=RANK(E7,$E$2:$E$18,1)
8	42187.5	=RANK(E8,$E$2:$E$18,1)
9	41015.625	=RANK(E9,$E$2:$E$18,1)
10	25554.375	=RANK(E10,$E$2:$E$18,1)
11	10837.5	=RANK(E11,$E$2:$E$18,1)
12	20508.375	=RANK(E12,$E$2:$E$18,1)
13	0	=RANK(E13,$E$2:$E$18,1)
14	17320.875	=RANK(E14,$E$2:$E$18,1)
15	14449.875	=RANK(E15,$E$2:$E$18,1)
16	5779.95	=RANK(E16,$E$2:$E$18,1)
17	9195.375	=RANK(E17,$E$2:$E$18,1)
18	8361	=RANK(E18,$E$2:$E$18,1)

[Speed67]

In the sample it is solve however the column F is not used in the function so when it changes or 3 is no longer the threshold/criterion then what. Ideally I would be able to have a way to enter a number in a field like 7 for the accumulated sum of the column F with maybe the other criteria for columns and it would figure out that 3 is the criterion for the rank and this formula would work but it seems that would be circular referencing.

[alansidman]

The way I figured it was you have 12 people in the Accounting Dept. You want to save X dollars. You insert a number to test, ie. 3 thus eliminating 8 bodies and determine your savings. If the number does not meet managements desire, change the number and test again. Note the math on the head count is always off by one. Really, can't think of another way to do this except trial and error.

Maybe someone else has some thoughts here.

[Speed67]

The question I get asked is what if we close these 3 divisions, how many accountants would we eliminate and what would that save if we were to only select the least senior of all our accountants across the organization.
Thanks for you assistance and have a wonderful Covid free weekend!!!.