Requesting for a nested formula to allot the amount

Currently I have multiple people in slab 1 and get the same amount

Instead, I want,
If the person is in slab 1 then if it has to look for the rank and then allot the amount between 100000 to 90000
and if there is no slab 2 then it can allot the slab 2 amount in slab itself

Eg
Now we have CCC, IIII and JJJ in slab one and get 10000

But I need KKKK to get 100000, IIII to get the next amount 9500 or 9000 based on the total numbers in the slab

FYI, I have 38 members in this list and I am filtering using the Month

You will asked by a moderator to change your title to something which describes what your problem is (as per a Google search).

Administrative Note:

Welcome to the forum. As John has warned you, here is your moderation requirement. Your thread title is still not satisfactory. Read the forum rules.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

I need KKKK to get 100000, IIII to get the next amount maybe 9500 or 9000 based on the total numbers in the slab

"Maybe" is rather vague: you need to show , with sample results, how a "slab" (whatever a slab is!) is allocated.

The Slab is allocated based on all the set criteria's mentioned to them, and it will have a rank between 1 to 38 (1 being the highest)
Only if they qualify in the criteria they will in slab 1 to 4 and those who miss will be in slab 5

So I need the formula to check if they slab 1 to 4 and if it is yes, then check which slab and if two or three getting the same slab then it should look for their rank( Ascending order) and allot the amount.
Say Slab 1 we have 4 people then slab amount 10k should be divided by 4, respectively the other slabs

The thread title is still woefully poor and completely generic. It needs to tell us what you are trying to do. Try again, please.

Does the title look good..?

Yes, it's OK - thanks. Please don't use vague, generic titles in future.

Please be patient. If someone can help, they will. You may bump your thread tomorrow if you get no help today.

Please refer to post #4: show expected (manually calculated) results.

Can anyone please advise.?

For the example given in post #1, it seems that the formula for cells H5 and down could be: =IFNA(VLOOKUP(G5,$L$5:$P$9,3+IF(G5=1,SUMPRODUCT((I$5:I$16<I5)*(G$5:G$16=1))),0),0)
Note that cells O6:P6 contain amounts to be given to the 2nd and 3rd ranked people in slab 1. If those amounts are not correct they may be manually changed.
Let us know if you have any questions.

As John has asked, please provide us with a manually calculated example so that we will have something on which to base our formulas/code.

(from post #18) Is it possible to formulate the slab*amount divided by total in the specific slab and allot based on the rank?

Based on the values in post #15 it would seem that the total slab amount of slab 1 is 38,250 if someone is in slab 2.
If there is no one in slab 2, then the total amount of slab 1 is 34,000.
Is that correct?

L11:N16 are filled manually and represent my understanding of the Slab setup.
K11:K16 are populated using: =IF(O11=TRUE,MID(L11,6,1)+0,"")
O11:O15 are populated using: =IF(LEN(L11)>6,AND(COUNTIFS(G$5:G$16,MID(L11,6,1)+0),COUNTIFS(G$5:G$16,RIGHT(L11,1))))
P11:P16 are populated using: =IF(O11=FALSE,"",COUNTIFS(G$5:G$16,MID(L11,6,1)+0))
Q11:U16 are populated using: =IF($O11=FALSE,"",IF(Q$10>$P11,"",$M11-($M11-$N11)/($P11-1)*(Q$10-1)))
H5:H16 are populated using: =IFNA(INDEX(Q$11:U$16,MATCH(G5,K$11:K$16,0),MATCH(J5,Q$10:U$10,0)),"")
J5:J16 are populated using: =SUMPRODUCT((G$5:G$16=G5)*(I$5:I$16<I5))+1
Let us know if you have any questions.

Thank you for your reply.
In O11:O15 are populated using: =IF(LEN(L11)>6,AND(COUNTIFS(G$5:G$16,MID(L11,6,1)+0),COUNTIFS(G$5:G$16,RIGHT(L11,1))))
P11:P16 are populated using: =IF(O11=FALSE,"",COUNTIFS(G$5:G$16,MID(L11,6,1)+0))

Why are we considering >6..?

The slabs that we are considering is only between 1 to 4 and anything >5 means the candidated will not be qualifying .

As to post #24 LEN(L11)>6 is counting the number of characters in cell L11, not the number of slabs.
If there are more than 6 characters then the formula preforms the AND function.
By the way I found an error with that formula. It should read: =IF(LEN(L11)>6,AND(COUNTIFS(G$5:G$16,MID(L11,6,1)+0),COUNTIFS(G$5:G$16,RIGHT(L11,1))),COUNTIFS(G$5:G$16,RIGHT(L11,1))>0)

Added 3 columns.
Column F is populated using: =SUMPRODUCT((C$2:C$12=C2)*(D$2:D$12<D2))+1
Column H is populated using: =IF(I3="","",IF(COUNTIFS(C$2:C$12,I3),I3,H2))
Column P is populated using: =SUMIFS(O$3:O3,H$3:H3,H3)
Note that the formula in column O is modified to read: =ROUND(M3*$O$2,0)
Column E is populated using: =INDEX(K$3:K$8,AGGREGATE(15,6,(ROW(K$3:K$8)-ROW(K$2))/(H$3:H$8=C2)/(P$3:P$8>=F2),1))
Let us know if you have any questions.

Thanks a lot.

In column E, Aggregate 15,6 is it the values from column O AND F .?

Also in Cell P4, the value shows 7, but I'm getting the result 4, could you please recheck that.


=@INDEX(K$3:K$8,AGGREGATE(15,6,(ROW(K$3:K$8)-ROW(K$2))/(H$3:H$8=C2)/(P$3:P$8>=F2),1))
aggregator formula in Column E, is not working in the Googlesheet ?

Not sure that I understand what is meant by "is it the values from column O AND F".
The INDEX AGGREGATE formula finds the correct amount in column K based on the values in column H matching the value in column C of that row and the values in column P matching the value in column C of that row.
The values in column P are cumulative so P4 is the total of the 3 people who receive 10000 and the 4 who receive 8000. the column P values are compared to the values in column F to put the correct values in column E.
In the future, please inform us if the formulas need to work for google sheets.
Here is an array formula that does the same thing as the INDEX AGGREGATE formula: =INDEX(K$3:K$8,SMALL(IF(H$3:H$8=C2,IF(P$3:P$8>=F2,ROW(K$3:K$8)-ROW(K$2))),1))
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.