Somme à conditions alternatives

[Charly boy]

Bonjour,

Au vu de mon précédent message resté sans réponse, je me permets de rouvrir une discussion car je crois ne pas avoir été très clair. Je m'explique donc à nouveau:

J'aimerais réussir à faire une somme à condition alternative. Autrement dit, Je voudrais que ne soit additionné dans la colonne E que les données qui renvoient à une condition 1 et à une condition 2 ou une condition 3. Donc que la cellule E3 ne soit prise en compte que Si A3= N2 et B3=O3 ou 04.

Pour l'instant, j'ai utilisé la formule suivante :

=SOMME.SI.ENS('A toi de jouer'!$E$3:$E$1000;'A toi de jouer'!$A$3:$A$1000;'A toi de jouer'!N3;'A toi de jouer'!$B$3:$B$1000;OU('A toi de jouer'!$O$3;'A toi de jouer'!$O$4))

mais elle ne fonctionne pas telle que je l'ai inscrite. Quelqu'un aurait-il une réponse?

Je vous joins le fichier pour vous aider à comprendre. Il s'agit de la formule dans la Colonne C dans la Feuille "Bilan par semaine".

Et pour le calcul, il s'agit de calculer le Nbre de Repas (feuil 'A toi de jouer', colonne E) en fonction des différentes semaines mais seulement les repas qui correspondent à la qualité (colonne B) Pris en charge ou Non-payants.

Merci de votre aide

CharlO

 

[Grand Chaman Excel]

Re : Somme à conditions alternatives

Bonjour Charly boy,

Une solution en utilisant la fonction matricielle SOMMEPROD, à valider avec CTRL + MAJ + ENTER.
Pour comprendre les conditions avec SOMMEPROD les * sont des ET et les + sont des OU...

Explication de la formule en C3:
=SOMMEPROD(
('A toi de jouer'!$E$3:$E$1000)* ==>Nombre de repas
('A toi de jouer'!$A$3:$A$1000='Bilan par semaine'!$A3)* ==>Condition 1 = No de Semaine
('A toi de jouer'!$B$3:$B$1000='A toi de jouer'!$O$3)+('A toi de jouer'!$B$3:$B$1000='A toi de jouer'!$O$4)) ==>Condition 2 : Qualité des repas

A+

 

[Charly boy]

Re : Somme à conditions alternatives

Merci beaucoup Grand Chaman,merci pour le vocabulaire SOMMEPROD

Ceci dit, quelque chose cloche encore dans l'attribution des qualités. Quand le calcul est relatif à la même qualité ("pris en charge" ou "Non payants") mais dès que les 2 qualités sont présentes dans la colonne B de la feuille 'A toi de jouer', les calculs ne se font plus comme il faut et ça peu importe le fait que la condition semaine soit respectée! ... Pour moi, c'est un mystère

Je te rejoins la feuille de calcul au cas où tu comprennes d'où vient le problème

 

[Grand Chaman Excel]

Re : Somme à conditions alternatives

Bonjour Charly boy,

Problème résolu, il manquait une parenthèse pour encadrer les 2 conditions OU :

=SOMMEPROD(
('A toi de jouer'!E$3:E$1000)*
('A toi de jouer'!A$3:A$1000='Bilan par semaine'!$A3)*
(('A toi de jouer'!B$3:B$1000='A toi de jouer'!$O$4)+('A toi de jouer'!B$3:B$1000='A toi de jouer'!$O$3)))

Désolé pour cet oubli...
A+

 

[Charly boy]

Re : Somme à conditions alternatives

Nickel, ça fonctionne parfaitement,

Merci beaucoup!

CharlO

 

[Charly boy]

Re : Somme à conditions alternatives

Le temps que tu m'aides grand Chaman, peux-tu me donner un autre petit conseil sur le même tableau?

Dans la feuille "A toi de jouer", dans la colonne D (identité), j'aimerais intégrer de nouvelles listes en cascade différentes en fonction de certaines conditions présentes dans la colonne C. En effet, je voudrais que si C3=Résidence alors D3= liste de résidence (présente dans la colonne O) ou si C3= Diffusion alors D3= Liste de diffusion(pst dans la colonne P).

On m'a aidé à en faire précédemment à partir du gestionnaire de noms mais je n'ai pas bien compris la formule. Dans "Validation des données", il y a source = "=INDIRECT(SUBSTITUE(SUBSTITUE(B6;" ";"_";1);" ";"_";1))". Peux-tu m'expliquer et me dire comment faire une nouvelle liste déroulante à condition ?

Merci d'avance pour tes éclaircissements que j'attends avec grande impatience!

Bonne journée

CharlO

 

[Grand Chaman Excel]

Re : Somme à conditions alternatives

Bonjour Charly Boy,

Je vais essayer de t'expliquer le principe des listes en cascades...

- Prenons ta cellule B8 = "Pris en charge",
- Tu veux que ta cellule C8 contienne les éléments de ta liste de validation qui se situe en Q2:Q10 et qui est un champ nommé "Pris_en_Charge"
- Si tu n'avais qu'une seule liste de validation, ta formule de ta liste de validation en C8 serait "=Pris_en_Charge", mais comme ta formule en C8 dépend de la valeur en B8, tu dois utiliser la fonction INDIRECT.
- La formule Indirect, permet de donner une référence spécifiée par une chaîne de caractère. Ainsi, si tu tapes INDIRECT("Pris_en_Charge"), c'est comme si tu pointais sur ta plage Q2:Q10.
- En C8, tu ne peux pas simplement écrire comme formule de validation "=INDIRECT(B8)" car le texte de B8 est "Pris en charge" et ta liste se nomme "Pris_en_charge". La différence est les espaces entre les mots, les champs nommés ne peuvent avoir d'espace.
- Pour cette raison, on utilise la formule SUBSTITUE pour remplacer les "espaces" dans B8 par des "_"
- Comme tu as 2 espaces en B8, tu dois imbriquer 2 fois la fonction SUBSTITUE et ta formule de validation devient donc : "=INDIRECT(SUBSTITUE(SUBSTITUE(B8;" ";"_";1);" ";"_";1))".

Pour ta liste en colonne D, c'est la même chose,
- Supposons qu'en C8, tu as "Prêts de salle améliorés".
- Tu veux en D8 que ta liste de validation pointe sur ta plage nommée "Prêts_de_salle_améliorés" Je l'ai renommé pour que les accents soient identiques car tu avais "PRETS_DE_SALLE_AMÉLIORES" (sans accent)
- Comme tu as 3 espaces entre les mots, tu devras imbriquer 3 fois la fonction SUBSTITUE dans ta formule de validation en colonne D. Ce qui devient :
=INDIRECT(SUBSTITUE(SUBSTITUE(SUBSTITUE(C8;" ";"_";1);" ";"_";1);" ";"_";1))

Voici ton fichier en retour. J'ai modifié le noms de ta plage nommée "Résidence" aussi.
Fais attention aux espaces entre les mots et aux accents quand tu utilises des listes en cascades.

J'espère que mon explication te permettras de mieux comprendre les listes de validation en cascades...😉.

A+