Problème avec la formule Excel "sommeprod"Résolu

Question

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

cs_MPi · Accepted Answer

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

cs_MPi · Answer

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

cs_MPi · Answer

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

idieordeco · Answer

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

idieordeco · Answer

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

cs_MPi · Answer

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

idieordeco · Answer

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

idieordeco · Answer

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

cs_MPi · Answer

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

PCPT · Answer

....déplacé de VB6 vers VBA!
merci de faire attention à la catégorie

Problème avec la formule Excel "sommeprod"

10 réponses

Votre réponse

Discussions similaires