Somme conditionelle qui ignore les critères vides

Bonjour à tous,

J'ai une jolie colle pour vous! Je cherche à développer un outil statistique facilement utilisable par mes employés qui comptabilise un nombre de clients selon plusieurs critères. SOMME.SI.ENS ne m'offre pas satisfaction principalement parce que certaines plages de critères doivent pouvoir rester vides, et donc ces critères doivent être ignorés par la formule si vides.

Je m'explique. J'ai un tableau source semblable à celui-ci qui répertorie différents critères relatifs aux groupes de clients.

ABCDEF
1ServicePrécisionType clientProvenanceLangueNombre de gens
2ForfaitDétenteAge d'orQuébecFrançais23
3ForfaitDétenteTouristesOntarioAnglais13
4ForfaitAnti-stressTravailleursAutreFrançais7
5ForfaitAnti-stressSportifsQuébecFrançais11
6AccèsCompletTouristesQuébecAnglais9
7AccèsCompletAge d'orOntarioFrançais12
8AccèsJardinFamilleAutreFrançais5
9AccèsJardinFamilleAutreFrançais8
10AbonnementTravailleursOntarioAnglais13
11AbonnementSportifsQuébecFrançais6

J'ai aussi un tableau de saisie de données où mes employés saisissent les critères à prendre en considération pour effectuer la somme conditionnelle.

IJ
4ServiceForfait
5PrécisionDétente
6Type clientToursites
7ProvenanceOntario
8LangueAnglais
9
10Résultat (nombre de gens)13

Jusqu'ici, une SOMME.SI.ENS en J10 peut faire le travail.

=SOMME.SI.ENS(F2:F11;A2:A11;J4;B2:B11;J5;C2:C11;J6;D2:D11;J7;E2:E11;J8)

Mais voilà; cette formule n'est efficace que lorsque l'ensemble des critères sont présents. De mon côté, je souhaite que la somme soit évolutive en fonction des critères présents ou non. Par exemple, "Anglais" en J8 avec aucun autre critère en J4:J7 devrait renvoyer à la valeur 35 en J10 (ce qui représente tous les Anglais).

Puis, en ajoutant le critère "Ontario" en J7 on devrait pouvoir avoir le résultat 26 (ce qui représente les Anglais de l'Ontario). Et ainsi de suite par élimination successive et sans avoir à respecter un ordre dans la saisie de données (on doit pouvoir commencer par n'importe quel critère et pas seulement celui de la langue comme dans l'exemple ici).

Avec ma formule actuelle, dès qu'il manque un critère entre J4:J8, le résultat renvoie à 0

Mon fichier source est en pièce jointe pour vous permettre de faire des essais.

31statistiques.xlsx (45.60 Ko)

Merci mille fois de votre aide!

Bonsoir, une autre approche avec la fonction Sous-total.

@+

Approche intéressante! Merci. Je l'utiliserais si je ne trouvais pas d'autres solutions qui nécessitent moins de manipulations.

En effet, je ne crois pas que l'ensemble des personnes qui manipuleront ce document seront à l'aise avec la manipulation des filtres et j'aimerais leur offrir un environnement simplifié au maximum.

S'il vous vient d'autres idées pour créer cette intervention au sein d'une fonction plutôt qu'en détournant le tri par une mise en forme du tableau, je serais preneur!

a voir avec la fonction sommeprod ou une somme matriciel.

@+

Le même problème persiste avec ces deux avenues : dès que l'ensemble des cellules de critères ne sont pas pleines, le résultat donne 0.

Bonjour,

Tu peux tester tes critères ...

En espèrant que cela t'aide ...

8statistiques.xlsx (14.79 Ko)

Bonjour à tous,

Une autre approche nécessitant Excel 2010+, avec un TCD et des segments pour le choix des critères.

Cdlt.

15statistiques-1.xlsx (27.43 Ko)

une autre façon de voir

7statistiques.xlsm (31.56 Ko)

Bonjour,

pré-rempli tous tes critères avec une "*" et c'est bon. Ils comprendront que c'est ce qu'il faut mettre pour 'Tous'.

Et tant qu'à faire tu peux y mets une liste de choix qui commence par *, ça évitera les erreurs de saisie

Cependant tes champs vides dans Précision ne sont pas comptabilisés, il faudrait y mettre * ou Autres

eric

Merci eriiic pour cette option. Je crois qu'il s'agit de la plus simple pour mes besoins.

J'ignorais que le caractère * était interprété par Excel comme «tous les éléments», c'est très utile!

Merci aussi à tous les autres qui m'ont fait découvrir de nouvelles fonctions et de nouvelles façons de mettre en page mes éléments pour les rendre plus lisibles! Je n'hésiterai pas à revenir si j'ai d'autres questions.

Je déclare ce sujet résolu!

j'ai ajouté une colonne numéro pour que le tableau s'allonge et que la saisie de nouvelles lignes ne se fassent pas avec des fautes de frappe ( ex : compet- complet, il y avait 2 écritures différentes pour age d 'or)

il faut rentrer les nouvelles valeurs dans la feuille données

17statistiques.xlsm (34.44 Ko)
Rechercher des sujets similaires à "somme conditionelle qui ignore criteres vides"