Forum Discussion

Copper Contributor

Sep 26, 2019

COUNTIFS avec criteres multiples

Bonjour, Je suis entrain de realiser un tableau excel et je me confronte a un probleme: je souhaite compter tous les camions provenant de tel fournisseur (Liste de fournisseur) arrivant le matin,...

Formulas and Functions

al_BAN

Copper Contributor

Sep 30, 2019

SergeiBaklan

Merci beaucoup pour ce retour!

J'ai finalement trouve la bonne synthaxe pour la formule qui est celle que vous proposez. Je me heurte a une autre difficulte:

Si maintenant je souhaite compter les fournisseurs d'une liste de 20 fournisseurs. Dois-je entre un a un la liste des fournisseurs ou je peux utiliser une formule de ce genre:

=COUNTIFS(Inbound! H:H,DashBoard!A6,Inbound!P:P,{Supplier!C21:C41})

Je n'arrive pas a trouver un moyen plus synthetique, pour le moment je rentre les criteres manuellement ce qui est tres repetitifs.

Pour le moment j'ai des formules qui ressemblent a cela:

=COUNTIFS(Inbound!$AF:$AF,"Yes",Inbound!$H:$H,DashBoard!$A17, Inbound!$E:$E, DashBoard!E$4,Inbound!P:P, "<>"&Supplier!C40,Inbound!P:P, "<>"&Supplier!C43, Inbound!P:P, "<>"&Supplier!C41, Inbound!P:P, "<>"&Supplier!C42, Inbound!P:P, "<>"&Supplier!C34,Inbound!P:P, "<>"&Supplier!C35, Inbound!P:P, "<>"&Supplier!C36, Inbound!P:P, "<>"&Supplier!C37, Inbound!P:P, "<>"&Supplier!C38, Inbound!P:P, "<>"&Supplier!C39)

Vous pouvez constater que c'est assez illisible !

Je decouvre tout juste la puissance d'excel! Merci pour votre aide.

Merci encore pour votre aide!

SergeiBaklan

Diamond Contributor

Oct 01, 2019

al_BAN

You may use

=SUM(COUNTIFS(Inbound! H:H,DashBoard!A6,Inbound!P:P,Supplier!C21:C41))

al_BAN
Copper Contributor
Oct 02, 2019
SergeiBaklan
It's not working...

I tried but the result is 0 each time.

Are you sure your formula is correct?

Thank you for your help, I really appreciate.
- SergeiBaklan
  Diamond Contributor
  Oct 02, 2019
  al_BAN
  
  Perhaps I misunderstood your requirements. Let check on a simple sample
  
  Formula calculates how many suppliers are in column P which are in the range C4:C6 and for which value in column H is equal to the value in cell A6.
  - al_BAN
    Copper Contributor
    Oct 25, 2019
    SergeiBaklan
    
    Hi, I just realised that I didn't give an update on that topic.
    
    Thank you a lot for your answer and sorry for the delay for my answer...
    
    I sorted the problem like that:
    
    =SUMPRODUCT(COUNTIFS(range1,criteria1,range2,criteria2,range3,criteria3,range4,criteria4,supplier range, Supplier))
    
    For the supplier range, I used a range named Supplier which contain the list of my suppliers.
    
    So pretty much exactly what you gave me. Instead I use a sumproduct because with the SUM it wasn't working.
    
    Thank you very much for your help.