Accueil

Nb.Si(champCritère;critère)
Somme.si(champCritère;Critère;champSomme)

NbsiSommesi.xls

La fonction Nb.Si() compte le nombre de cellules vérifiant le critère spécifié.

La fonction Somme.Si() calcule la somme des cellules vérifiant le critère.

Sur l'exemple, le critère est dans la cellule G2

Critère avec un joker *

=NB.SI(Champ;"*FR*") donne le nombre de cellules contenant le code FR

Dans l'exemple ci dessous,nous comptons le nombre de codes contenant FR1,FR2,FR3

En G7 : =NB.SI(B2:B21;"*"&F3)
En H7: =SOMME.SI(B2:B21;"*"&F3;C2:C21

Appartenance à un ensemble

Compter le nombres de cellules qui contiennent aa,bb,cc.

=SOMMEPROD(NB.SI(A2:B12;{"aa";"bb";"cc"}))
=SOMMEPROD(NB.SI(A2:B12;D7:D9))

Sous-Totaux

En C2:=SI(A2<>A3;SOMME.SI($A$2:$A$100;A2;$B$2:$B$100);"")

Somme si montant coché

=SOMME.SI(C3:M3;"þ";B3:L3)

SommeMontantCoché

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count = 1 And Target.Column <= 13 And Target.Column Mod 2 = 1 And Target.Row >= 3 And Target.Row <= 6 Then
     Target = IIf(Target = "þ", "o", "þ") 'Police Wingdings
  End If
End Sub

Autre exemple

=SOMME.SI($A$2:$G$6;A10;$B$2:$H$6)

SommeSi plusieurs colonnes

On veut la somme des colonnes B,C,D pour lesquelles les lignes contient X en colonne A.

Somme.Si Plusieurs Colonnes

=SOMMEPROD(SOMME.SI(A2:A10;"x";DECALER(B2:B10;;{0;1;2})))

Totaux avec table de correspondance

SommeSi

Sélectionner H2:H5
=totalserieMat(G2:G4;correspond;ident;heures)
Valider avec maj+ctrl+entrée

Function totalserieMat(noSerie, Correspond, ident, heures)
  Application.Volatile
  t = ident
  h = heures
  a = Correspond
  s = noSerie
  Set d1 = CreateObject("Scripting.Dictionary")
  For i = LBound(a) To UBound(a)
    d1(a(i, 2)) = a(i, 1)
  Next i
  Dim ts()
  ReDim ts(LBound(s) To UBound(s))
  For k = LBound(s) To UBound(s)
    For i = LBound(t) To UBound(t)
      tmp = t(i, 1)
      ns = d1(tmp)
        If ns = s(k, 1) Then
           ts(k) = ts(k) + h(i, 1)
        End If
      Next i
   Next k
   totalserieMat = Application.Transpose(ts)
End Function

Nb.Si.Ens(champCritère1;critère1;champCritère2;critère2;...)
Somme.Si.Ens(ChampSomme;champCritère1;Critère1;champCritère2;critère2;...)

Remplace SommeProd() avec plusieurs critères.

NbSiEns SommeSiEns.xls
SommeSiEns
SommeSiEns 2 Crit

En G2: =NB.SI.ENS(E2:E21;">15000";D2:D21;"thomas")
En G7: =SOMME.SI.ENS(E2:E21;E2:E21;">15000";D2:D21;"thomas")
En G11:=SOMME.SI.ENS(E2:E21;C2:C21;"sud";D2:D21;"thomas")
En G16: =NB.SI.ENS(A2:A21;">01/06/2006";D2:D21;"thomas")

Joker *

Somme pour région commençant par X

=SOMME.SI.ENS(E2:E21;C2:C21;"X*")

Avec les tableaux dynamiques structurés, les formules deviennent

En G2: =NB.SI.ENS(Tableau1[CA];">15000";Tableau1[Vendeur];"thomas")
En G7: =SOMME.SI.ENS(Tableau1[CA];Tableau1[CA];">15000";Tableau1[Vendeur];"thomas")
En G11:=SOMME.SI.ENS(Tableau1[CA];Tableau1[Région];"sud";Tableau1[Vendeur];"thomas")
En G16: =NB.SI.ENS(Tableau1[DateVente];">01/06/2006";Tableau1[Vendeur];"thomas")

NbSiEns SommeSiEns.xls

Tableaux dynamiques structurés et Indirect

A2 contient le nom d'un tableau dynamique.

=SOMME.SI.ENS(INDIRECT(A2&"[Nb km]");INDIRECT(A2&"[Dates]");">="&$F$1;INDIRECT(A2&"[Dates]");"<="&$H$1)

Tableaux dynamiques et indirect

Plsieurs valeurs pour le critère

Plusieurs valeurs critère

liste1 ={201;203;205}

=SOMMEPROD(SOMME.SI.ENS(A2:A17;B2:B17;liste1))

2 critères

liste1 ={201;203;205}
liste3 ={0.46.50}

=SOMMEPROD(SOMME.SI.ENS(A2:A17;B2:B17;liste1;C2:C17;liste3))