Home

COUNTIFS avec criteres multiples

%3CLINGO-SUB%20id%3D%22lingo-sub-876534%22%20slang%3D%22fr-FR%22%3ECOUNTIFS%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876534%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20in%20the%20process%20of%20making%20an%20excellent%20table%20and%20I%20face%20a%20problem%3A%20I%20want%20to%20count%20all%20the%20trucks%20from%20such%20supplier%20(Supplier%20List)%20arriving%20in%20the%20morning%2C%20afternoon%20etc.%3C%2FP%3E%3CP%3ELet's%20start%20with%20a%20concrete%20case%3A%3C%2FP%3E%3CP%3EI%20have%20my%20first%20sheet%20with%20the%20gives%2C%20the%20second%20with%20the%20list%20of%20suppliers%20and%20the%20third%20with%20my%20COUNTIFS.%20I%20enter%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%22COUNTIFS%22%20(Inbound!%20H%3AH%2CDashBoard!%20A6%2CInbound!%20P%3AP%2C%22Supplier!%20B12%22%2C%20%22Supplier!%20B14%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20I%20do%20not%20get%20the%20right%20result%20to%20my%20calculation%2C%20same%20if%20I%20try%20with%20the%20exact%20names%3A%3C%2FP%3E%3CP%3E%22COUNTIFS%22%20(Inbound!%20H%3AH%2CDashBoard!%20A6%2CInbound!%20P%3AP%2C%22supplier%201%2C%22%20%22supplier%202%22)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20other%20hand%2C%20I%20get%20the%20right%20result%20if%20I%20do%20count%20deliveries%20for%20a%20single%20supplier%3A%3C%2FP%3E%3CP%3E%22COUNTIFS%22%20(Inbound!%20H%3AH%2CDashBoard!%20A6%2CInbound!%20P%3AP%2C%20supplier%201%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20an%20idee%20of%20where%20the%20problem%20may%20come%20from%3F%20knowing%20that%20all%20the%20fields%20I%20use%20are%20%22text%22%20type.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDon't%20hesitate%20if%20you%20need%20more%20details%2C%20I%20don't%20feel%20like%20I've%20been%20clear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20in%20advance%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-876534%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876632%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20avec%20criteres%20multiples%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415565%22%20target%3D%22_blank%22%3E%40al_BAN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20try%20to%20count%20suppliers%20which%20are%20simultaneously%20Supplier1%20AND%20Supplier2%2C%20correct%20formula%20is%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Inbound!%20H%3AH%2CDashBoard!A6%2CInbound!P%3AP%2C%22supplier1%22%2CInbound!P%3AP%2C%22supplier2%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20that%20has%20no%20sense%20from%20logic%20point%20of%20view.%20If%20to%20count%26nbsp%3BSupplier1%20OR%20Supplier2%20than%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Inbound!%20H%3AH%2CDashBoard!A6%2CInbound!P%3AP%2C%7B%22supplier1%22%2C%22supplier2%22%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewith%20constants%2C%20or%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Inbound!%20H%3AH%2CDashBoard!A6%2CInbound!P%3AP%2C%22supplier1%22)%2BCOUNTIFS(Inbound!%20H%3AH%2CDashBoard!A6%2CInbound!P%3AP%2C%22supplier2%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882621%22%20slang%3D%22fr-FR%22%3ERe%3A%20COUNTIFS%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882621%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20coming%20back!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20finally%20found%20the%20right%20synthax%20for%20the%20formula%20which%20is%20the%20one%20you%20propose.%20I%20run%20into%20another%20difficulty%3A%3C%2FP%3E%3CP%3EIf%20now%20I%20want%20to%20count%20the%20suppliers%20of%20a%20list%20of%2020%20suppliers.%20Do%20I%20have%20to%20enter%20one%20has%20a%20list%20of%20suppliers%20or%20I%20can%20use%20a%20formula%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Inbound!%20H%3AH%2CDashBoard!A6%2CInbound!P%3AP%2C%7BSupplier!C21%3AC41%7D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20not%20find%20a%20more%20synthetic%20way%2C%20for%20the%20moment%20I%20enter%20the%20criteres%20manually%20which%20is%20very%20repetitive.%3C%2FP%3E%3CP%3EAt%20the%20moment%20I%20have%20formulas%20that%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(Inbound!%24AF%3A%24AF%2C%22Yes%22%2CInbound!%24H%3A%24H%2CDashBoard!%24A17%2C%20Inbound!%24E%3A%24E%2C%20DashBoard!E%244%2CInbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C40%2CInbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C43%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C41%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C42%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C34%2CInbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C35%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C36%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C37%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C38%2C%20Inbound!P%3AP%2C%20%22%26lt%3B%26gt%3B%22%26amp%3BSupplier!C39)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20see%20that%20it's%20quite%20unreadable!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20discovered%20the%20power%20of%20excel!%20Thank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887354%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20avec%20criteres%20multiples%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415565%22%20target%3D%22_blank%22%3E%40al_BAN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUM(COUNTIFS(Inbound!%20H%3AH%2CDashBoard!A6%2CInbound!P%3AP%2CSupplier!C21%3AC41))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888836%22%20slang%3D%22fr-FR%22%3ERe%3A%20COUNTIFS%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888836%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20not%20working...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20but%20the%20result%20is%200%20each%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20sure%20your%20formula%20is%20correct%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help%2C%20I%20really%20appreciate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888895%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20avec%20criteres%20multiples%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F415565%22%20target%3D%22_blank%22%3E%40al_BAN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20I%20misunderstood%20your%20requirements.%20Let%20check%20on%20a%20simple%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20423px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135114i4BBD8B79E0CEDB57%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFormula%20calculates%20how%20many%20suppliers%20are%20in%20column%20P%20which%20are%20in%20the%20range%20C4%3AC6%20and%20for%20which%20value%20in%20column%20H%20is%20equal%20to%20the%20value%20in%20cell%20A6.%3C%2FP%3E%3C%2FLINGO-BODY%3E
al_BAN
New Contributor

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, l'apres-midi etc.

Partons sur un cas concret:

J'ai ma premiere feuille avec les donnees, la deuxieme avec la liste des suppliers et la troisieme avec mon COUNTIFS. Je rentre la formule suivante:

=COUNTIFS(Inbound!H:H,DashBoard!A6,Inbound!P:P,{"Supplier!B12","Supplier!B14"})

 

et je n'obtiens pas le bon resultat a mon calcul, pareil si j'essaye avec les noms exactes:

=COUNTIFS(Inbound!H:H,DashBoard!A6,Inbound!P:P,{"fournisseur 1","fournisseur 2"}) 

 

En revanche, j'obtiens le bon resultat si je ne compte les livraisons pour un seul fournisseur:

=COUNTIFS(Inbound!H:H,DashBoard!A6,Inbound!P:P,"fournisseur 1")

 

Avez-vous une idee d'ou peut provenir le probleme? sachant que tous les champs que j'utilise sont de type "text".

 

N'hesitez pas si vous avez besoin de plus de details, je n'ai pas l'impression d'avoir ete bien clair.

 

merci par avance pour votre aide.

5 Replies

@al_BAN 

If you try to count suppliers which are simultaneously Supplier1 AND Supplier2, correct formula is like

=COUNTIFS(Inbound! H:H,DashBoard!A6,Inbound!P:P,"supplier1",Inbound!P:P,"supplier2")

but that has no sense from logic point of view. If to count Supplier1 OR Supplier2 than like

=COUNTIFS(Inbound! H:H,DashBoard!A6,Inbound!P:P,{"supplier1","supplier2"})

with constants, or

=COUNTIFS(Inbound! H:H,DashBoard!A6,Inbound!P:P,"supplier1")+COUNTIFS(Inbound! H:H,DashBoard!A6,Inbound!P:P,"supplier2")

@Sergei Baklan 

 

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!

 

@al_BAN 

You may use

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

 

@Sergei Baklan 

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.

@al_BAN 

Perhaps I misunderstood your requirements. Let check on a simple sample

image.png

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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies