Looking for Formula for excel-CodePudding

ProductName | display | final value
Lewis           No
Lewis           No
Lewis           No
Sood            No
Henny           Yes
Henny           No
Henny           No

1.Looking for a formula in excel where if product have display value as yes with same product name then final value should come as yes and other should come as variant. 2.else if the product have display value no for all products with same name then it should come as not displayed.

Final output should be

ProductName | display | final value

Lewis           No.     Not display
Lewis           No.     Not display
Lewis           No.     Not display
Sood            No.     Not display
Henny           Yes.    Yes
Henny           No.     Variant
Henny           No.     Variant

Tried with if and count if but not able to find not displayed logic

CodePudding user response：

Give a try on the following formula-

=LET(x,CONCAT(UNIQUE(FILTER($B$2:$B2,$A$2:$A2=A2))),IF(x="No","Not Display",IF(x="Yes","Yes","Variant")))

CodePudding user response：

In older Excel use: =IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"Yes"),IF(B2="Yes","Yes","Variant"),"Not display")

It checks for the combination of the name and Yes being present anywhere in the range. If FALSE it results in Not display; if TRUE it checks if the value in column B is Yes, than it will result in Yes. Otherwise it will result in Variant.