IF function

[kradclayton]

Hi All,

I need help on a multiple tiered IF function.

I have a current IF formula - =IF(I2<5,5,CEILING(I2/3,10)) however I now need to add another criteria to the formula to look at disregarding this formula if the minimum of date created is newer than 01/01/2021, in which case id like it to return a 10.

here's the below columns I'm using:

Minimum of fdate created = H
Count of Samples purchase LTM = I

Minimum of Date Created	Count of Samples purchase LTM
11-Jul-16​	350​
28-Jul-16​	321​
11-Jul-16​	318​

 

[jasonb75]

Maybe

=IF(H2>=DATE(2021,1,1),10,IF(I2<5,5,CEILING(I2/3,10)))

 

[kradclayton]

Great thank you! Is there a way to also caveat the date criteria by if the date is newer than 01,01,2021 AND count is higher than 10 then continue with the IF<5,5,CELING(I2/3,10)?

 

[jasonb75]

Like this?

=IF(AND(I2<5,H2<DATE(2021,1,1)),5,CEILING(I2/3,10)))

The logic in your posts is contradicting itself so I've had to take a guess on what is actually required.