Formula that checks 4 different parameters before giving a solution

[probexcel]

Hi, I am have a hard time coming up with a solution for my problem and I'm hoping someone here can guide me.

I have a dataset structured like that:

Product	Pack	Year	Total
iPhone 6	Box	2016	10
iPhone 6	Box	2017	13
iPhone 7	Box	2017	11
iPhone 7	No Box	2018	11
iPhone 8	No Box	2020	9
iPhone 8	No Box	2021	12
iPhone 9	Box	2019	6

I want to add a 5thcolumn that with the following output

Product	Pack	Year	Total	Check
iPhone 6	Box	2015	2
iPhone 6	Box	2016	10
iPhone 6	Box	2017	13	Hold
iPhone 7	Box	2017	11
iPhone 7	No Box	2018	11
iPhone 8	No Box	2020	9
iPhone 8	No Box	2021	12
iPhone 9	Box	2019	6

Explaining:

1. Lines 2, 3, and 4 - If I have the same product AND same package, then I want to check if any of the OLDEST products is greater than or equals 10. If it is, then I want to hold the most recent product
   1. iPhone 6 2015 is the most recent product? NO. iPhone 6 2015 has a total of 10 or higher? NO. I don't care.
   2. iPhone 6 2016 is the most recent product? NO. iPhone 6 2016 has a total of 10 or higher? YES. Check Next.
   3. iPhone 6 2017 is the most recent product? YES. iPhone 6 of the older vintages has a total of 10 of higher? YES. iPhone 6 2016 has a total of 10. Then HOLD IT.
2. Lines 5 and 6 - If I have the same product, but different package, I don't care.
3. Lines 7 and 8 - If I have the same product AND same package, but none of the oldest ones are 10 or higher in the total column, I also don't care.
   1. iPhone 8 2020 is the most resent product? NO. iPhone 8 2020 has a total of 10 or higher? NO. I don't care, skip product.
4. Line 9 - If there's no duplicate, I also don't care.

Is there a way to do that with Excel formulas?

TY!

 

[Legacy 143009]

3. Lines 7 and 8 - If I have the same product AND same package, but none of the oldest ones are 10 or higher in the total column, I also don't care.

1. iPhone 8 2020 is the most resent product? NO. iPhone 8 2020 has a total of 10 or higher? NO. I don't care, skip product.

But iPhone 8 2021 is a more recent product and has more than 10. What is the rule?

iPhone 8

No Box

2021

12

 

[probexcel]

But iPhone 8 2021 is a more recent product and has more than 10. What is the rule?

This is the tricky part, the oldest one needs to be over 10, and in this case is 9.

 

[probexcel]

But iPhone 8 2021 is a more recent product and has more than 10. What is the rule?

IF Count(Product & Package) > 1 AND IF MIN(Year for that Product & Package) > Total 10 THEN MAX(Year for that Product & Package) = Hold

For iPhone 8 I have --> Count(Product & Package) > 1 (TRUE) AND MIN(Year for that Product & Package = 2020) > Total 10 (FALSE, Total is 9) THEN nothing

 

[Legacy 143009]

Ok. Now I get it. One last question. What if older model (with the same package) has more than 10 but the most recent is 9?