SOLVED

Dénombrement selon plusieurs critères

%3CLINGO-SUB%20id%3D%22lingo-sub-1578271%22%20slang%3D%22fr-FR%22%3ECounting%20according%20to%20several%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578271%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20problem%20counting%20lines%20with%20different%20criteria%3A%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E1%20name%20(string%20of%20characters)%20among%203%20in%20column%20G%3C%2FLI%3E%3CLI%3E1%20or%202%20statuses%20(string%20of%20characters)%20among%204%20in%20column%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20various%20attempts%20at%20formulas%20send%20me%20back%20%220%22%20as%20a%20result%20in%20the%20final%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20advance%2C%20thank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1578271%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578307%22%20slang%3D%22en-US%22%3ERe%3A%20D%C3%A9nombrement%20selon%20plusieurs%20crit%C3%A8res%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754754%22%20target%3D%22_blank%22%3E%40Belz29%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20very%20difficult%20to%20visualize%20this%20without%20actually%20seeing%20your%20spreadsheet.%20Is%20it%20possible%20for%20you%20to%20post%20a%20copy%20of%20it%2C%20just%20making%20sure%20no%20private%20or%20confidential%20information%20is%20included.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578319%22%20slang%3D%22fr-FR%22%3ERe%3A%20Counting%20according%20to%20several%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578319%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Mathetes%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20very%20reactive%20response.%3C%2FP%3E%3CP%3EThe%20simplified%20file%20as%20an%20attachment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1580386%22%20slang%3D%22fr-FR%22%3ERe%3A%20Counting%20according%20to%20several%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1580386%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%2C%3C%2FA%3E%20I%20finally%20managed%20to%20find%20the%20solution%20on%20my%20own.%3C%2FP%3E%3CP%3EThe%20formula%20NB.SI.%20ENS%20allowed%20me%20to%20automate%20my%20chart%2C%20saving%20valuable%20minutes%20each%20week%20(file%20corrected%20as%20attachment).%3C%2FP%3E%3CP%3ENice%20day!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581194%22%20slang%3D%22en-US%22%3ERe%3A%20D%C3%A9nombrement%20selon%20plusieurs%20crit%C3%A8res%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581194%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754754%22%20target%3D%22_blank%22%3E%40Belz29%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20done.%20I%20have%20added%20a%20slight%20modification%20to%20yours%20in%20the%20attached.%20You%20had%20%22hard-coded%22%20the%20variables%20into%20your%20COUNTIFS%20formulas.%20I%20made%20those%20references%20to%20other%20cells%2C%20and%20that%20makes%20it%20possible%20to%20copy%20the%20same%20formula%20from%20cell%20D5%20into%20all%20of%20the%20cells%20I've%20highlighted%20with%20the%20light%20green%20background.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1597150970809.png%22%20style%3D%22width%3A%20586px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211760i618CA1864DFDC9A7%2Fimage-dimensions%2F586x164%3Fv%3D1.0%22%20width%3D%22586%22%20height%3D%22164%22%20title%3D%22mathetes_0-1597150970809.png%22%20alt%3D%22mathetes_0-1597150970809.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581271%22%20slang%3D%22fr-FR%22%3ERe%3A%20Counting%20according%20to%20several%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581271%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BA%20big%20thank%20you%20for%20your%20help!%20Referring%20to%20cells%20rather%20than%20their%20value%20will%20indeed%20help%20me%20for%20future%20developments%20(the%20file%20is%20much%20more%20complete%20and%20complex%20than%20the%20sample%20I%20sent%20you).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582469%22%20slang%3D%22en-US%22%3ERe%3A%20D%C3%A9nombrement%20selon%20plusieurs%20crit%C3%A8res%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754754%22%20target%3D%22_blank%22%3E%40Belz29%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20general%2C%20you%20should%20avoid%20%22hard-coding%22%20values%20into%20formulas.%20That%20always%20places%20restrictions%20on%20any%20future%20use%2C%20any%20expansion...%26nbsp%3B%20And%20so%20often%20values%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20take%20an%20example%2C%20if%20you%20had%20a%20figure%20for%20%22sales%20tax%22%20that%20has%20been%20the%20same%20for%20the%20last%20two%20or%20three%20(or%20twenty)%20years%2C%20it%20still%20could%20change%20next%20year.%20A%20lot%20better%20to%20refer%20to%20a%20cell%20that%20contains%20the%20value%2C%20than%20to%20have%20that%20value%20written%20into%20each%20formula%20that%20uses%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20of%20all%2C%20use%20a%20named%20range%20so%20that%20your%20formula%20reads%20%3DProdCost%20*%20SalesTax%26nbsp%3B%20and%20then%20when%20SalesTax%20is%20changed%2C%20the%20formula%20automatically%20is%20up-to-date.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Bonjour,

 

Je rencontre un problème pour dénombrer des lignes contenant différents critères : 

  • 1 nom (chaîne de caractères) parmi 3 en colonne G
  • 1 ou 2 statuts (chaîne de caractères) parmi 4 en colonne 

Les différentes tentatives de formules me renvoient "0" comme résultat dans la cellule finale.

 

Par avance, merci de votre aide.

6 Replies

@Belz29 

 

It's very difficult to visualize this without actually seeing your spreadsheet. Is it possible for you to post a copy of it, just making sure no private or confidential information is included.

@mathetes 

Bonjour Mathetes,

 

Merci pour votre réponse très réactive.

Le fichier simplifié en pièce jointe.

Best Response confirmed by Belz29 (New Contributor)
Solution

Bonjour,

 

Merci @mathetes , j'ai finalement réussi à trouver la solution par mes propres moyens.

La formule NB.SI.ENS m'a permis d'automatiser mon tableau, et ainsi de gagner de précieuses minutes chaque semaine (fichier corrigé en pièce jointe).

Bonne journée!

@Belz29 

 

Well done. I have added a slight modification to yours in the attached. You had "hard-coded" the variables into your COUNTIFS formulas. I made those references to other cells, and that makes it possible to copy the same formula from cell D5 into all of the cells I've highlighted with the light green background.

mathetes_0-1597150970809.png

 

@mathetes Un grand merci pour votre aide! Faire référence à des cellules plutôt qu'à leur valeur va en effet m'aider pour les prochaines évolutions (le fichier est bien plus complet et complexe que l'échantillon que je vous ai transmis).

@Belz29 

 

In general, you should avoid "hard-coding" values into formulas. That always places restrictions on any future use, any expansion...  And so often values change.

 

To take an example, if you had a figure for "sales tax" that has been the same for the last two or three (or twenty) years, it still could change next year. A lot better to refer to a cell that contains the value, than to have that value written into each formula that uses it.

 

Best of all, use a named range so that your formula reads =ProdCost * SalesTax  and then when SalesTax is changed, the formula automatically is up-to-date.