HOW TO GET MY LOGICAL ANSWER

Question

Need Answer According these Criteria

1. IF Excitel Match in Brand Discount should be 50% and if 2 bill is same then
2. Discount should be 50% in Lowest Value of Rate and if 3 Bill is Same then
3. Discount Should be 33.33% in Lowest Value of Rate and if 4 Bill No is Found
4. Same then Discount Should be 25% in Lowest 2 Value of Rate and other Will be 25%

Answer 1

Hi,

Try this formula:

=IF(COUNTIF($A$2:$A$34,A2)=1,0.25,IF(AND(COUNTIF($A$2:$A$34,A2)=2,MINIFS($D$2:$D$34,$A$2:$A$34,A2)=D2),0.5,IF(AND(COUNTIF($A$2:$A$34,A2)=3,MINIFS($D$2:$D$34,$A$2:$A$34,A2)=D2),0.3333,IF(AND(COUNTIF($A$2:$A$34,A2)=4,OR(SUMPRODUCT(($A$2:$A$34=A2)((D2+ROW()/1000)<($D$2:$D$34+(ROW($M$2:$M$34)/1000))))+1=3,SUMPRODUCT(($A$2:$A$34=A2)((D2+ROW()/1000)<($D$2:$D$34+(ROW($M$2:$M$34)/1000))))+1=4)),0.25,"--"))))

Hope it's helpful.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.