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

6 réponses

  1.
    wilfried_42 Messages postés 912 Statut Contributeur 245
     
    Bonjour

    essaye cela

    =SOMMEPROD((gauche(A2:A23,3)="mlj") * ( droite(A2:A23)="10") * (B2:B23="informatique") * (C2:C23))
    0
  2. wilfried_42 Messages postés 912 Statut Contributeur 245
     
    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
  3. wilfried_42 Messages postés 912 Statut Contributeur 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
    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
  4. wilfried_42 Messages postés 912 Statut Contributeur 245
     
    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. Vous n’avez pas trouvé la réponse que vous recherchez ?

    Posez votre question
  6. wilfried_42 Messages postés 912 Statut Contributeur 245
     
    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
  7. wilfried_42 Messages postés 912 Statut Contributeur 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
    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

Discussions similaires

tout en majuscules (excel)
txiki -
Avatar -

35 réponses