If a row contains more then 3 numbers

[Lancer7]

Wondering If there is a simple way to do a formula based on how many numbers are in a column. For example in Cell B:50 I have a number 5500, If A:1 to A:20 has 3 different numbers anywhere within that range I want it to subtract 5% from 5500 giving me 5225, If A:1 To A:20 Has 4 different numbers I want it to subtract 6%, If A1:A20 has 5 different numbers I want it to subtract 7%. on repeat all the way from 3 different numbers up to 7. What Im trying to build is a rebate calculator and the more "Categories" a customer buys from the higher rebate they get the range A1:A20 is the category numbers so there a customer could buy from say category 2,6, and 7. That would be 3 categories for the 5% rebate

 

[Eric W]

Try:

=LET(c,COUNT(A1:A20),p,IF(c<3,0,MIN(c,7)+2)/100,B50*(1-p))

 

[Lancer7]

Try:

=LET(c,COUNT(A1:A20),p,IF(c<3,0,MIN(c,7)+2)/100,B50*(1-p))

That is it!! Thank you

 

[Eric W]

Glad to help!