Excel (formule : SOMMEPROD et SOMME.SI)

Résolu
marie -  
 marie -
Bonjour,

Je vais vous expliquer mon souci à travers un exemple :
j'ai un tableau avec 3 colonnes (colonne A : référence, colonne B : service, colonne C : montant)



Je veux additionner tous les montants correspondants aux références (mlj0010 ; mlj1110 ; mlj2210 ; mlj3310).

J'ai essayé 2 formules :
- la 1ère, en détaillant chaque références (mais la formule est trop longue!!!),
- et la 2ème, j'ai inséré une étoile pour avoir tous les références commençant par mlj et finissant par 10.

=(SOMME.SI(A2:A23;"mlj0010";C2:C23))+(SOMME.SI(A2:A23;"mlj1110";C2:C23))+(SOMME.SI(A2:A23;"mlj2210";C2:C23))+(SOMME.SI(A2:A23;"mlj3310";C2:C23))


=SOMME.SI(A2:A23;"mlj*10";C2:C23)

Cela fonctionne (mes 2 formules fonctionnent très bien !!!).




Maintenant, je voudrais additionner tous les montants correspondants aux références (mlj0010 ; mlj1110 ; mlj2210 ; mlj3310) et qui ont comme service (informatique).
Donc, ici j'ai 2 conditions (j'utilise la formule SOMMEPROD).

=(SOMMEPROD((A2:A23="mlj0010")*(B2:B23="informatique")*(C2:C23)))+(SOMMEPROD((A2:A23="mlj1110")*(B2:B23="informatique")*(C2:C23)))+(SOMMEPROD((A2:A23="mlj2210")*(B2:B23="informatique")*(C2:C23)))+(SOMMEPROD((A2:A23="mlj3310")*(B2:B23="informatique")*(C2:C23)))


=SOMMEPROD((A2:A23="mlj*10")*(B2:B23="informatique")*(C2:C23))

La 1ère formule fonctionne mais pas la derniére (le système ne comprend pas mlj*10).


MA QUESTION : comment faire fonctionner ma derniére formule (pour m'éviter d'avoir une très longue formule de calcul)?


Merci beaucoup

Marie
A voir également:

6 réponses

Réponse 1 / 6
wilfried_42 Messages postés 907 Date d'inscription   Statut Contributeur Dernière intervention   245
 
Bonjour


essaye cela

=SOMMEPROD((gauche(A2:A23,3)="mlj") * ( droite(A2:A23)="10") * (B2:B23="informatique") * (C2:C23))
0
Réponse 2 / 6
wilfried_42 Messages postés 907 Date d'inscription   Statut Contributeur Dernière intervention   245
 
oops une tite erreur

=SOMMEPROD((gauche(A2:A23,3)="mlj") * (droite(A2:A23,2)="10") * (B2:B23="informatique") * (C2:C23))
0
marie
 
Merci Wilfried, je viens d'essayer (ça fonctionne avec une petite erreur à corriger)


=SOMMEPROD((GAUCHE(A2:A23;3)="mlj")*(DROITE(A2:A23;2)="10")*(B2:B23="informatique")*(C2:C23))

mettre des points virgules au lieu des virgules.


Mais merci énormément.

Une petite question par rapport à ta formule : (A2:A23;3) le chiffre 3 correspond au nombre de caractéres qui compose le mot (mlj) ?

Merci

Marie
0
Réponse 3 / 6
wilfried_42 Messages postés 907 Date d'inscription   Statut Contributeur Dernière intervention   245
 
re:

oui c'est bien cela, si tu as un nombre de caractere qui correspond à la valeur d'une cellule, tu remplaces 3
par nbcar(macellule)
0
marie
 
Bonjour
J'ai une autre question (concernant la formule de calcul SOMMEPROD)

J'ai toujours un tableau avec 3 colonnes (A: nom de communes ; B: Privé/Public ; C: Surface en m2).
En ligne 1 : j'ai les entêtes des colonnes et mes données commencent en ligne 2.
voir ci-dessous exemple (mon tableau d'origine est composé de 800 lignes environ) :


Nom de communes Privé/Public Surface en M²
lyonmkk01129999 privé 1
lyonyu59999 privé 2
lyonui9999 privé 3
lyonyj55j4j9999 privé 4
lyonu9999 privé 5
lyonttttt999912 privé 6
lyonzzz9999133 privé 7
lyon999911 privé 8
lyon9999 privé 9
lyon11ee9999 privé 10
lyon9999 privé 11
lyonmkk01129999 public 1
lyonyu59999 public 2
lyonui9999 public 3
lyonyj55j4j9999 public 4
lyonu9999 public 5


Je veux additionner toutes les surfaces des communes commençant par "lyon" et finissant par "9999" (avec pour mention privé).

Donc ça j'arrive à le faire (c'est ce que tu m'as expliqué hier)

=SOMMEPROD((GAUCHE(A2:A17;4)="lyon")*(DROITE(A2:A17;4)="9999")*(B2:B17="privé")*(C2:C17))


Mais, j'ai 3 lignes dans mon tableau qui ne sont pas prises car "9999" n'est pas situé tout à fait à droite. Il y a une suite.

lyonttttt999912 privé 6
lyonzzz9999133 privé 7
lyon999911 privé 8

MA QUESTION : Existe t'il une formule qui pourrait prendre toutes les cellules composées de "lyon" situé à gauche et prendre "9999" situé au milieu (toujours avec 1 deuxiéme condition "privé") ?

Merci d'avance
Marie
0
Réponse 4 / 6
wilfried_42 Messages postés 907 Date d'inscription   Statut Contributeur Dernière intervention   245
 
re:

à essayer mais sans ganrantie

=SOMMEPROD((GAUCHE(A2:A17;4)="lyon")*(A2:A17="*9999*")*(B2:B17="privé")*(C2:C17))
0
marie
 
ça ne fonctionne pas.

Merci
0

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

Posez votre question
Réponse 5 / 6
wilfried_42 Messages postés 907 Date d'inscription   Statut Contributeur Dernière intervention   245
 
re:

autre essai

=SOMMEPROD((GAUCHE(A2:A17;4)="lyon") * NON(ESTERREUR(CHERCHE("9999";A2:A17))) *(B2:B17="privé") * (C2:C17))
0
marie
 
Merci cela fonctionne (le résultat attendu est le bon).

Mais peux-tu m'expliquer cette formule :
NON(ESTERREUR(CHERCHE("9999";A2:A17)))

pourquoi utiliser la fonction ESTERREUR ?

Merci
Marie
0
Réponse 6 / 6
wilfried_42 Messages postés 907 Date d'inscription   Statut Contributeur Dernière intervention   245
 
re:

Cherche("9999";A2:A17) si la chaine n'existe pas, il renvoie une erreur, sinon il renvoie la position de la chaine dans le texte

ce qui nous interesse c'est de savoir si la chaine est dedans mais sa position on s'en moque
donc en testant si ca renvoie une erreur on sait que la chaine n'y est pas
Esterreur(.........) renvoie vrai s'il y a une erreur est faux s'il n'y en a pas

mais notre interet dans cette formule est de prendre l'inverse de l'erreur d'ou le NON(essterreur(........))
0
marie
 
ouh la la !!! complexe (un peu dure à comprendre) mais je pense avoir compris l'idée.

Je te remercie beaucoup d'avoir pris du temps pour la résolution de cette formule.

Merci
Marie
0