Problème avec la formule Excel "sommeprod"

Résolu
Signaler
Messages postés
28
Date d'inscription
mercredi 7 novembre 2007
Statut
Membre
Dernière intervention
20 février 2011
-
Messages postés
13280
Date d'inscription
lundi 13 décembre 2004
Statut
Modérateur
Dernière intervention
3 février 2018
-
Bonjour,

Dans le cadre de mon boulot j'utilise tout le temps la formule "sommeprod" de façon à additionner les valeurs répondant aux critères de ma formule.
Cependant j'ai problème avec...
Un problème sur la longueur de la formule à cause d'un grand nombre de critères pour additionner.

Exemple d'une formule (volontairement raccourci) :
SOMMEPROD((Inventaire!$L$2:$L$2000)*(Inventaire!$M$2:$M$2000= "Emprunt d'état"))+SOMMEPROD((Inventaire!$L$2:$L$2000)*(Inventaire!$M$2:$M$2000="Garanti"))
Dans cette formule je cherche à additionner les valeurs de la colonne L si dans la colonne M il y a écrit "emprunt d'état" + additionner les valeurs de la colonne L si dans la colonne M il y a écrit "Garanti" (et ça pour chaque ligne de 2 à 2000)

Jusqu'à la, pas de soucis mais ! Y a t'il possibilité de mettre dans un seul sommeprod les critères "garanti" et "emprunt d'état" ???

J'ai essayé ça :
SOMMEPROD((Inventaire!$L$2:$L$2000)*( et ((Inventaire!$M$2:$M$2000 ="Emprunt d'état");(Inventaire!$M$2:$M$2000= "Garanti"))
Ne marche pas (puisqu'il faut les deux critères dans la même cellule (emprunt d'état et Garanti) et ça c'est impossible.

J'ai essayé ça :
SOMMEPROD((Inventaire!$L$2:$L$2000)*( ou ((Inventaire!$M$2:$M$2000 ="Emprunt d'état");(Inventaire!$M$2:$M$2000="Garanti"))
Ne marche pas ! (Et je ne comprends pas pourquoi d'ailleurs).
Dans ce cas il m'additionne tout même quand il n'y a pas "emprunt d'état" ou "garanti" dans la colonne M (je peux mettre "tartiflette" par exemple et il me l'additionne)

Voila ou j'en suis, quelqu'un a t'il une idée pour raccourcir ma formule du début ?

Merci pour tout

Bière qui roule bière qui mousse

10 réponses

Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
19
Essaie ceci
Ça semble fonctionner
SOMMEPROD(((Inventaire!$M$2:$M$2000"Emprunt d'état") + (Inventaire!$M$2:$M$2000"Garanti"))*(Inventaire!$L$2:$L$2000))

Attention aux parenthèses qui entourent les conditions et pas la formule globale.

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
19
Premièrement, tu n'as pas besoin de répéter la colonne que tu veux additionner (ici L)
SOMMEPROD((Inventaire!$M$2:$M$2000="Emprunt
d'état") *(Inventaire!$M$2:$M$2000="Garanti")*(Inventaire!$L$2:$L$2000))

Syntaxe:
SOMMEPROD((1ere condition)*(2e condition)*(3e condition)*(Plage à additionner))

Tu pourrais aussi utiliser une colonne vide et y concaténer toutes les conditions pour finalement faire un SOMME.SI sur cette colonne...

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
19
Je disais de concaténer, mais dans ton cas, comme tu sembles vérifier les conditions dans une même colonne, tu pourrais plutôt mettre une formule OU, du genre:
SI(OU(M2"Emprunt d'état";M2="Garanti"); "X"; "")

Ce qui aurait pour but d'inscrire un "X" si une des conditions est remplie. Et faire un SOMME.SI sur les "X"

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
28
Date d'inscription
mercredi 7 novembre 2007
Statut
Membre
Dernière intervention
20 février 2011

Merci pour ta réponse mais la solution
"SOMMEPROD((Inventaire!$M$2:$M$2000="Emprunt d'état") *(Inventaire!$M$2:$M$2000="Garanti")*(Inventaire!$L$2:$L$2000))"
ne marche pas
Il cherche dans la colonne M garanti et emprunt d'etat.

La formule longue d'origine est celle là chez moi :
(SOMMEPROD((Inventaire!$B$2:$B$200077002)*(Inventaire!$I$2:$I$2000="UEM")*(Inventaire!$L$2:$L$2000)*(Inventaire!$M$2:$M$2000="Emprunt d'état"))+SOMMEPROD((Inventaire!$B$2:$B$2000=77002)*(Inventaire!$I$2:$I$2000="UEM")*(Inventaire!$L$2:$L$2000)*(Inventaire!$M$2:$M$2000="Garanti")))/$B$23

Le résultat est 178.03%
Le but pour moi est de raccourcir la formule en virant le "+" et donc l'un des deux "sommeprod"

Avec ta proposition ça fait cela :
SOMMEPROD((Inventaire!$B$2:$B$200077002)*(Inventaire!$I$2:$I$2000="UEM")*(Inventaire!$L$2:$L$2000)*(Inventaire!$M$2:$M$2000="Emprunt d'état")*(Inventaire!$M$2:$M$2000="Garanti"))/$B$23

Le résultat est 0% (il cherche les 2 conditions dans la même cellule colonne M à chaque fois alors que c'est impossible : c'est soit l'un soit l'autre soit autre chose)

Il n'y a donc pas de solution avec le sommeprod ?
Je dois obligatoirement refaire une colonne avec des conditions (une sorte d'intermédiaire ?). Je peux faire ça mais bon... je vais le prévoir dans ma macro alors (qui importe des trucs et ces trucs sont triés par les sommeprod en fonction de critères etc.)

Bière qui roule bière qui mousse
Messages postés
28
Date d'inscription
mercredi 7 novembre 2007
Statut
Membre
Dernière intervention
20 février 2011

Question en plus qui m'intrigue :
Pour ce qui suis ne marche pas"SOMMEPROD((Inventaire!$L$2:$L$2000)*(ou((Inventaire!$M$2:$M$2000"Emprunt d'état") ; (Inventaire!$M$2:$M$2000"Garanti"))"
dans mon cas ?
Dans ce cas il m'additionne tout même quand il n'y a pas "emprunt d'état" ou "garanti" dans la colonne M (je peux mettre "tartiflette" par exemple et il me l'additionne)

Je suis chiant hein  mais ça m'intrigue que le "ou" ne marche pas

Bière qui roule bière qui mousse
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
19
Dans ton cas, comme les conditions sont dans une même colonne, tu n'as pas vraiment le choix que d'utiliser le +

Le * dans SOMMEPROD agit comme un ET
Peut-être y a-t-il moyen d'utiliser autre chose que le * pour avoir un OU, mais je connais pas...

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
28
Date d'inscription
mercredi 7 novembre 2007
Statut
Membre
Dernière intervention
20 février 2011

C'est pas grâve, merci pour ton aide.
Je creuse un peu là mais à part un "*" ou un ";" (qui revient au même), je ne vois pas trop quoi mettre entre les critères.

Donc je modifie la macro qui importe les données pour pré-trier la base de données.

A bientôt

Bière qui roule bière qui mousse
Messages postés
28
Date d'inscription
mercredi 7 novembre 2007
Statut
Membre
Dernière intervention
20 février 2011

Merci, ça marche parfaitement !

Je vais tenter voir si ça marche avec du multi conditions dans plusieurs colonnes ; genre :

sommeprod(((condition 1 colonne M)+(condition 2 colonne M))*((condition 1 colonne B)+(condition 2 colonne B))*(valeur colonne I))

Normalement oui.
En tout cas merci pour aide, bien alléger mes formules

Bière qui roule bière qui mousse
Messages postés
3877
Date d'inscription
mardi 19 mars 2002
Statut
Membre
Dernière intervention
23 août 2018
19
Si ça fonctionne, reviens nous montrer ta formule et accepte ta réponse. Ça pourra être utilie pour les prochains qui passeront...

MPi²
Pour ceux qui programment sous Office, n'oubliez pas qu'il existe un forum dédié à ces applications VBA....... ICI
Messages postés
13280
Date d'inscription
lundi 13 décembre 2004
Statut
Modérateur
Dernière intervention
3 février 2018
47
....déplacé de VB6 vers VBA!
merci de faire attention à la catégorie