Bonjour, souhait de faire une fonction SI

[afoucaul]

Bonjour 

Je suis sur un tableau fournisseur. 

Je souhaiterais ajouter une colonne ou dedans il y aurait deux choix : "oui" et "non". 
Le "oui" serait Si un fournisseur (colonne C) intervient dans plusieurs catégories (colonne E), c'est à dire 3 minimum. 

Si le fournisseur intervient dans moins de 3 catégories, alors un "non".

J'ai l'impression que les formules "SI" ne se font que si on cherche des Chiffres et dans mon cas, je cherche à mettre des conditions sur le nombre des différentes catégories par fournisseur. 

Auriez vous une idée pour m'aider svp ? Merci

[#2]

@afoucaul 

COUNTIFS works for text fields.

= MAP(Supplier, LAMBDA(s,
    COUNTA(UNIQUE(FILTER(Category, Supplier=s)))
  ))

Counts the number of categories that are listed for each supplier. Using a Lambda function, this could be expressed

= MAP(Supplier, CategoryCountλ)

Putting this together and one gets

= IF(MAP(Supplier, CategoryCountλ)>=3, "Oui", "Non")

[#3]

@afoucaul 

365 seulement

[#4]

@Peter Bartholomew 

The Excel is like that so when i write the formula; Neither the Supplier; neither Oui or Non works

[#5]

@afoucaul 

Hopefully this is a step in the right direction.

= IF(MAP(Fournisseur, CategoryCountλ)>=3, "Oui", "Non")

CategoryCountλ
= COUNTA(UNIQUE(FILTER(Category, Fournisseur = s)))

[#6]

@Peter Bartholomew 

Thank you so much because i understood your procedure, and i tried to apply it to my work, 
For C qnd G columns i used =UNIQUE(B11:B119871) and =UNIQUE(F11:F119871)
For D qnd H columns =NBVAL(UNIQUE(B11:B119871))

COUNTA in french is NBVAL. 

So now for the CategoryCountλ i got an error : 

Excel does'nt recognize the "categories" word i guess .... 
And it is the same for 

= IF(MAP(Fournisseur, CategoryCountλ)>=3, "Oui", "Non")

If you can help me to understand this mistake, after i will be good. 

Thanks

[#7]

I wrote Suppliers instead of Fournisseurs to be easier for you

[#8]

@afoucaul 

Just to check, I assume you are using Excel 365 since the tool tip seems to accept UNIQUE and offers the French word 'matrice' rather than the English 'array'.

'Categories' would need to defined in Name Manger to reference the green column of your table, just as 'supplier/fournisseur' refers to the blue column.  The error you show may be no more than a complaint about the "," (comma) being used as a separator whereas French localisation requires ";" (semi-colon).

Hopefully you will be allowed to post workbooks soon so that I am better able to examine your formulas.  My apologies for addressing you in English, regrettably my attempting French would not improve our communication.

[#9]

Hello, yes 365 company.

Ok, i'm going to work on it.

Thanks, have a good day

[#10]

alternatively by sql：

select Fournisseur,(select iif(count(distinct Category)>2,'Oui','Non') from count_distinct_category_of_each_suppliers b where a.Fournisseur=b.Fournisseur) Analyse,Category
from count_distinct_category_of_each_suppliers a;

[#11]

Ahah no worry for the french, i understood your explanation so don't worry