NB.SI avec plusieurs critères / conditions

Le problème

 « J’aimerai utiliser la fonction Sommeprod mais j’ai un souci, lorsque je veux prendre la totalité de ma colonne, il me mets un #NOMBRE. Comment puis-je remédier à ce problème ?

La solution

Houla, elle est compliquée ta question, mais sache que ton maître va y répondre !  … Attention aux ricannements petit scarabé, je pourrais m’énerver. Bon on commence ?
Première chose à dire, inutile d’essayer de le faire avec un NB.SI cumulé avec un ET, du style

=nb.si(A1:A100;et(« Titi »; « Gros Minet »)

Cela ne fonctionnera pas… Pour cela, il faut utiliser une formule qu’il faudra que vous reteniez. Cette formule est très puissante et on peut faire beaucoup de chose avec et notamment résoudre ce problème.
Il s’agit de la fonction

=SOMMEPROD()

Comment l’utilise t’on ? c’est très simple les amis, suivez le guide!

La construction de la fonction

Cette fonction est simple d’utilisation. Elle se construit de cette manière:

1
=SOMMEPROD((ZONE1=CRITERE1)*(ZONE2=CRITERE2)*(ZONE3=CRITERE3)*(ZONE N = CRITERE N))

Alors reprenons notre tableau. Si en G4 je veux avoir le nombre de fois où Titi à gagner le 1 janvier 2008 alors la formule sera la suivante:

1
=sommeprod((B1:B21=E4)*(C1:C21=F4))

Explication:

Nous avons besoin de la fonction sommeprod() que voici:

1
=sommeprod()

La zone1 = B1:B21, c’est à dire la colonne où je peux trouver E4

1
 =sommeprod((B1:B21=E4)

La zone2 = C1:C21, c’est à dire la colonne où il y a les dates.

1
=sommeprod((B1:B21=E4)*(C1:C21=F4))

Ainsi la formule trouvée est bien:

1
=sommeprod((B1:B21=E4)*(C1:C21=F4))

Somme.si avec plusieurs critères / conditions

Le problème

J’aimerai faire un somme.si mais avec plusieurs critères. J’ai cru comprendre qu’on pouvait utiliser la fonction sommeprod. Comment la construit t-on ?

La solution

Nous aimerions faire la somme de Thibaut et de Elo par date. Pour cela, nous avons donc besoin d’un somme.si avec plusieurs conditions. La première concernera le nom du joueur et la seconde, en toute logique, la date.

Qu’est-ce que je rabache sans cesse sur le site ?? quelle fonction est à connaitre sur le bout des doigts ?? oui, toi dans le fond, je t’écoute ? Oui, tout à fait, la fonction SOMMEPROD.

Dans cette astuce, nous nous servons de la fonction SOMMEPROD pour effectuer un NB.SI avec plusieurs conditions. Ici, nous allons l’utiliser pour un SOMME.SI.

On commence ? allez c’est parti.

Combien de points ont Elo et Thibaut par date ?

=SOMMEPROD()

Cette fonction est simple d’utilisation. Elle se construit de cette manière:

1
=SOMMEPROD((ZONE1=CRITERE1)*(ZONE2=CRITERE2)*(ZONE N = CRITERE N)*(ZONE A ADDITIONNER))

Nous pouvons ajouter jusqu’à 30 critères. Je pense que vous en aurez donc assez !! Ensuite, si vous avez plus de critères, vous devrez peut être passer par le VBA.

Ici, Pour Thibaut, cela donne donc pour la première zone :

1
=SOMMEPROD(($B$3:$B$17=$F3)

Ici, F3= Thibaut. Je préfère souvent mettre une cellule plutôt qu’un nom figé. Cela est plus facile si on souhaite le modifier.

Ensuite, on incorpore le second critère. Alors j’écoute ? qu’est-ce que je mets à la suite ?

La réponse est en faite toute simple et je pense que vous l’aviez trouvé :

1
=SOMMEPROD(($B$3:$B$17=$F3)*($C$3:$C$17=G$2)

Encore une fois, si on reprend la première image, on se rend compte que G2 = le deuxième critère, autrement dit, la date.

Enfin, je mets la zone que je veux additionner :

1
=SOMMEPROD(($B$3:$B$17=$F3)*($C$3:$C$17=G$2)*$D$3:$D$17)

En effet, D3:D17 comprend dans notre exemple le nombre de points de chaque joueur à additionner.

Attention, pour que cette formule fonctionne, il faut respecter quelques critères. Le premier : les zones doivent être de même dimensions. Inutile de mettre une zone de 10 lignes et une autre de 5 lignes. Cela ne fonctionnera pas. Deuxième critère: Elles doivent être alignées. En ce sens, si la première zone est A1:A10, les autres zones devront commencer à la ligne 1 et terminer à la ligner 10. si vous la faites commencer à la ligne 2 et terminer à la ligne 11, cela ne fonctionnera pas non plus !

Astuces pour les débutants sur Excel