Excel (formule : SOMMEPROD et SOMME.SI)

Résolu
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
Configuration: Excel 2002
Windows XP
Internet Explorer 6.0

6 réponses

  1.  wilfried_42 Contributeur
    Bonjour

    essaye cela

    =SOMMEPROD((gauche(A2:A23,3)="mlj") * ( droite(A2:A23)="10") * (B2:B23="informatique") * (C2:C23))
    0
    1. wilfried_42 Contributeur
      oops une tite erreur

      =SOMMEPROD((gauche(A2:A23,3)="mlj") * (droite(A2:A23,2)="10") * (B2:B23="informatique") * (C2:C23))
      0
      1. 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
    2. wilfried_42 Contributeur
      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
      1. 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
    3. wilfried_42 Contributeur
      re:

      à essayer mais sans ganrantie

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

        Merci
        0
    5. wilfried_42 Contributeur
      re:

      autre essai

      =SOMMEPROD((GAUCHE(A2:A17;4)="lyon") * NON(ESTERREUR(CHERCHE("9999";A2:A17))) *(B2:B17="privé") * (C2:C17))
      0
      1. 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
    6. wilfried_42 Contributeur
      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
      1. 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