High-Tech Santé Médecine Droit-Finances

Réalisation

vidéo numérique

Rechercher : dans
Par :

Excel (formule : SOMMEPROD et SOMME.SI)

Dernière réponse le 10 sep 2008 à 18:06:54 marie, le 9 sep 2008 à 17:59:17 
 Signaler ce message aux modérateurs

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

Configuration: Excel 2002
Windows XP
Internet Explorer 6.0

Meilleures réponses pour « excel (formule : SOMMEPROD et SOMME.SI) » dans :
Formules EXCEL de Base 2 VoirOn a vu la fonction SI La fonction ET est également interressante. Combinée à SI, celà donne : =SI(ET(condition1;condition2;.......conditionZ);vrai;faux) on affiche les différentes conditions à vérifier (ex : B3>C2 ....C2=B9; etc..). si toutes...
Formules Excel de base VoirVoici quelques formules de bases qui sont faciles à réaliser et qui peuvent donner un peu de vie à une feuille Excel ! Pour cette astuce, on supposera que l’on veut que le résultat s’affiche dans la cellule B10 et on se servira principalement des...
Eviter les doublons dans Excel VoirEviter les doublons lors d'un encodage dans un colonne de feuille excel: prendre le format conditionnel sur le première cellule sous les titres (par ex A2) choisir la formule suivante : =NB.SI($A$2:$A$21;A2)>1 mettre là la couleur de fond...
Posez votre question Répondre

1

wilfried_42, le 9 sep 2008 à 18:07:00

Bonjour


essaye cela

=SOMMEPROD((gauche(A2:A23,3)="mlj") * ( droite(A2:A23)="10") * (B2:B23="informatique") * (C2:C23))
Cordialement
Wilfried

   
Répondre à wilfried_42

2

wilfried_42, le 9 sep 2008 à 18:10:56

Oops une tite erreur

=SOMMEPROD((gauche(A2:A23,3)="mlj") * (droite(A2:A23,2)="10") * (B2:B23="informatique") * (C2:C23)) Cordialement
Wilfried

   
Répondre à wilfried_42

3

marie, le 9 sep 2008 à 18:23:27

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

   
Répondre à marie

4

wilfried_42, le 9 sep 2008 à 18:53:10

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) Cordialement
Wilfried

   
Répondre à wilfried_42

5

marie, le 10 sep 2008 à 13:09:27

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

   
Répondre à marie

6

wilfried_42, le 10 sep 2008 à 13:14:24

Re:

à essayer mais sans ganrantie

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

   
Répondre à wilfried_42

7

marie, le 10 sep 2008 à 13:18:50

ça ne fonctionne pas.

Merci

   
Répondre à marie

8

wilfried_42, le 10 sep 2008 à 15:10:23

Re:

autre essai

=SOMMEPROD((GAUCHE(A2:A17;4)="lyon") * NON(ESTERREUR(CHERCHE("9999";A2:A17))) *(B2:B17="privé") * (C2:C17))
Cordialement
Wilfried

   
Répondre à wilfried_42

9

marie, le 10 sep 2008 à 17:18:59

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

   
Répondre à marie

10

wilfried_42, le 10 sep 2008 à 17:39:32

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(........))
Cordialement
Wilfried

   
Répondre à wilfried_42

11

 marie, le 10 sep 2008 à 18:06:54

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

   
Répondre à marie
Posez votre question Répondre
Ils ont besoin de votre aide