Somme si (critère)

[Scott621]

Bonjour à tous,
Voici ma question :
Je souhaiterais utiliser la fonction somme.si, mais dans le critère, mettre : si ce n'est pas le critère "garçon", alors faire la somme.
Comment faire?
=SOMME.SI(B1:B6;PAS GARCON;A1:A6)

Vous remerciant de votre aide!

 

[Tibo]

Re : Somme si (critère)

Bonjour,

Avec ce que j'ai compris, essaye avec cette formule :

=SOMME.SI(B1:B6;"<>"&"GARCON";A1:A6)

Si pas ça, reviens avec un extrait de ton fichier

@+

Edit : Salut Jean-Claude 🙂

@+

 

[JCGL]

Re : Somme si (critère)

Bonjour à tous,

Peux-tu essayer avec :

=SOMME.SI(B1:B6;"<>"&"garçon";A1:A6)

A+ à tous

Édition : Salut Tibo 😎, même combat... A++

 

[Scott621]

Re : Somme si (critère)

Re Bonjour,
Alors la formule m'a pas mal aidé. Je souhaiterais aller plus loin avec plusieurs critères.
Pour l'instant, la formule qui fonctionne avec un seul critère est :
=SOMME.SI(B2:B55;"<>"&B61;D2😀55)
Si à la place de B61 je veux en mettre plusieurs, j'ai essayé
=SOMME.SI(B2:B55;"<>"&"B61:B70";D2😀55)
=SOMME.SI(B2:B55;"<>"&B61:B70;D2😀55)
Mais ca ne fonctionne pas...

Vous remerciant encore de votre aide!

 

[Tibo]

Re : Somme si (critère)

re,

Peux-tu nous joindre un extrait de ton fichier (sans données confidentielles) et en nous indiquant le résultat attendu.

Ca sera alors plus facile pour t'aider

A te (re)lire avec ce fichier exemple

@+

 

[Scott621]

Re : Somme si (critère)

Regarde la pièce jointe Forum 2011.xlsMerci de ton aide!
Il s'agit du montant en face de "Autre"
Je souhaiterais que la case fasse la somme comme pour A, B, C, D mais en ne les prenant pas en compte et en prenant en compte que le reste...

Dans l'attente de vous lire...

 

[Tibo]

Re : Somme si (critère)

re,

Un essai avec cette formule en C17 :

=SOMMEPROD((NON(ESTNUM(EQUIV($B$2:$B$8;$B$13:$B$16;0))))*$C$2:$C$8)

@+

 

[Scott621]

Re : Somme si (critère)

Re Tibo,
Alors bonne nouvelle, ta formule fonctionne. Mais je la trouve super compliquée pour moi si j'ai besoin d'y faire des modifications par la suite...
Est-il possible de la faire avec Somme.si.ens et des <> ?

 

[Tibo]

Re : Somme si (critère)

re,

Une solution avec SOMME.SI.ENS :

=SOMME.SI.ENS($C$2:$C$8;$B$2:$B$8;"<>"&"A";$B$2:$B$8;"<>"&"B";$B$2:$B$8;"<>"&"C";$B$2:$B$8;"<>"&"D")

Inconvénients :

- Il faut décrire les données à exclure : A, B, C et D (on peut mettre les références de cellules, mais ça revient à la même chose).

- Cette fonction est apparue avec Excel 2007. Le souci, c'est qu'elle n'est pas reconnue par les versions antérieures d'Excel, d'où ma réticence à l'utiliser.

@+

 

[Scott621]

Re : Somme si (critère)

Re,
Alors effectivement, cette formule ne fonctionne pas avec ma version de Excel.
Peux-tu m'expliquer en détails la formule Sommeprod que tu m'as montré qui fonctionne?

 

[Tibo]

Re : Somme si (critère)

re,

Bon, la formule est plus facile à faire (si si ! ) qu'à expliquer :

=SOMMEPROD((NON(ESTNUM(EQUIV($B$2:$B$8;$B$13:$B$16;0))))*$C$2:$C$8)

détail :

EQUIV($B$2:$B$8;$B$13:$B$16;0)

cette partie de formule retourne une matrice dont le résultat est :

{1;2;#N/A;3;4;2;#N/A}

(Pour voir ce résultat, sélectionner dans la barre de formule la partie de la formule qui nous intéresse et faire F9)

en y appliquant la fonction ESTNUM, on obtient cette nouvelle matrice :

{VRAI;VRAI;FAUX;VRAI;VRAI;VRAI;FAUX}

Pour Excel, VRAI vaut 1 et FAUX vaut 0

Ce qui nous intéresse, ce sont les lignes où le résultat est FAUX (donc 0). Pour pouvoir faire la multiplication, il faut transformer les VRAI en FAUX et les FAUX en VRAI. On y parvient avec la fonction NON qui inverse le résultat :

{FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;VRAI}

On multiplie cette matrice avec la plage de cellules (ou matrice) $C$2:$C$8, ce qui nous donne :

{0;0;24.54;0;0;0;14.27}

Il ne reste plus à Excel qu'à faire la somme de cette matrice avec la fonction SOMMEPROD

Euh, pas certain que ce soit bien clair, mais je te laisse manipuler cette formule par petits bouts, puis par coups de F9.

@+

 

[Scott621]

Re : Somme si (critère)

Y a-t-il un moyen de mettre une plage à la place de B62?
=SOMME.SI(B2:B55;"<>"&B62;D2😀55)

 

[Tibo]

Re : Somme si (critère)

re,

A ma connaissance, non.

Sinon, j'ai repris ton fichier et je te propose une autre formule (plus simple) basée également sur SOMMEPROD, mais associée cette fois à la fonction NB.SI :

=SOMMEPROD((NON(NB.SI(B13:B16;B2:B8))*C2:C8))

Là aussi, pour comprendre, sélectionner NB.SI(B13:B16;B2:B8) puis faire F9 pour voir la matrice correspondante dans la barre de formule. Le NON inverse la matrice. Et SOMMEPROD multiplie les deux matrices et les additionne.

@+

 

[Scott621]

Re : Somme si (critère)

Excellent! Cette formule me convient très bien...
Merci beaucoup