Problème avec la formule Excel "sommeprod"

Résolu
idieordeco Messages postés 28 Date d'inscription mercredi 7 novembre 2007 Statut Membre Dernière intervention 20 février 2011 - 5 avril 2008 à 11:20
PCPT Messages postés 13280 Date d'inscription lundi 13 décembre 2004 Statut Membre Dernière intervention 3 février 2018 - 6 avril 2008 à 15:45
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

cs_MPi Messages postés 3877 Date d'inscription mardi 19 mars 2002 Statut Membre Dernière intervention 23 août 2018 21
5 avril 2008 à 18:23
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
3
cs_MPi Messages postés 3877 Date d'inscription mardi 19 mars 2002 Statut Membre Dernière intervention 23 août 2018 21
5 avril 2008 à 14:50
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
0
cs_MPi Messages postés 3877 Date d'inscription mardi 19 mars 2002 Statut Membre Dernière intervention 23 août 2018 21
5 avril 2008 à 14:55
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
0
idieordeco Messages postés 28 Date d'inscription mercredi 7 novembre 2007 Statut Membre Dernière intervention 20 février 2011
5 avril 2008 à 15:58
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
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
idieordeco Messages postés 28 Date d'inscription mercredi 7 novembre 2007 Statut Membre Dernière intervention 20 février 2011
5 avril 2008 à 16:01
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
0
cs_MPi Messages postés 3877 Date d'inscription mardi 19 mars 2002 Statut Membre Dernière intervention 23 août 2018 21
5 avril 2008 à 16:09
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
0
idieordeco Messages postés 28 Date d'inscription mercredi 7 novembre 2007 Statut Membre Dernière intervention 20 février 2011
5 avril 2008 à 18:07
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
0
idieordeco Messages postés 28 Date d'inscription mercredi 7 novembre 2007 Statut Membre Dernière intervention 20 février 2011
6 avril 2008 à 13:37
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
0
cs_MPi Messages postés 3877 Date d'inscription mardi 19 mars 2002 Statut Membre Dernière intervention 23 août 2018 21
6 avril 2008 à 14:53
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
0
PCPT Messages postés 13280 Date d'inscription lundi 13 décembre 2004 Statut Membre Dernière intervention 3 février 2018 49
6 avril 2008 à 15:45
....déplacé de VB6 vers VBA!
merci de faire attention à la catégorie
0