I would like to calculate a "bulk" rebate.
They way I need this to happend is that once you have bought more then eg. 5 times a product you get a 10% discount as of the 6th (not on the onces lower then 6) product. Once you buy more the eg.10 times the same product. (etc)
So I was thinking of using a formula like =SWITCH(COUNTIF(B:B;B2);<5;"no rebate";COUNTIF(B:B;B2);<10;5%;.....)
But problem I see there is that this will also change the older inputs I made.
So is there a way to make it so that the first eg 5 times it encounters product A it stays unchanged for those ones, between 5 and 10 unchanged, ect.
It woul be even better if I could combine that with the date . As then I could count only within a specific period.... (eg in first QRT I did 12 times product A. The first 5 should be full price, 5 to 10 should have a rebate of 5% and the last 2 should have 10% rebate. Next Quarter we start the count again. But all is in one table....
They way I need this to happend is that once you have bought more then eg. 5 times a product you get a 10% discount as of the 6th (not on the onces lower then 6) product. Once you buy more the eg.10 times the same product. (etc)
So I was thinking of using a formula like =SWITCH(COUNTIF(B:B;B2);<5;"no rebate";COUNTIF(B:B;B2);<10;5%;.....)
But problem I see there is that this will also change the older inputs I made.
So is there a way to make it so that the first eg 5 times it encounters product A it stays unchanged for those ones, between 5 and 10 unchanged, ect.
It woul be even better if I could combine that with the date . As then I could count only within a specific period.... (eg in first QRT I did 12 times product A. The first 5 should be full price, 5 to 10 should have a rebate of 5% and the last 2 should have 10% rebate. Next Quarter we start the count again. But all is in one table....
| Date | Product | rebate |
01/05/2022 | A | ? |
30/04/2022 | A | ? |
20/02/2021 | B | ? |
03/06/2022 | C | |
15/06/2022 | B | |
01/05/2022 | A | |
30/04/2022 | B | |
20/02/2021 | A | |
03/06/2022 | C | |
| … | … | |
