Comment utiliser SUBTOTAL avec SUMIF dans Excel

Vous pouvez utiliser la formule suivante pour combiner les fonctions SOUS-TOTAL et SUMIF dans Excel :

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C11)-ROW(C2),,1)),--(B2:B11="Guard"))

Cette formule particulière vous permet de additionner les valeurs de la plage C2:C11 où la valeur correspondante dans la plage B2:B11 est égale à « Garde » même après que cette plage de cellules a été filtrée d’une manière ou d’une autre.

L’exemple suivant montre comment utiliser cette formule dans la pratique.

Exemple : Comment utiliser SUBTOTAL avec SUMIF dans Excel

Supposons que nous disposions de l’ensemble de données suivant contenant des informations sur divers joueurs de basket-ball :

Filtrons ensuite les données pour afficher uniquement les lignes où se trouvent les joueurs de la conférence Ouest .

Pour ce faire, mettez en surbrillance la plage de cellules A1:C11 . Cliquez ensuite sur l’onglet Données le long du ruban supérieur et cliquez sur le bouton Filtrer .

Cliquez ensuite sur la flèche déroulante à côté de Conférence et assurez-vous que seule la case à côté de Ouest est cochée, puis cliquez sur OK :

Les données seront automatiquement filtrées pour afficher uniquement les lignes où la colonne Conférence est égale à Ouest :

Si nous essayons d’utiliser la fonction SUMIF() pour additionner les valeurs de la colonne Points où la valeur de la colonne Position est égale à « Guard », elle renverra en fait la somme des points pour les lignes égales à « Guard » dans la fonction SUMIF(). ensemble de données d’origine :

Au lieu de cela, nous devons utiliser la formule suivante :

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C11)-ROW(C2),,1)),--(B2:B11="Guard"))

La capture d’écran suivante montre comment utiliser cette formule en pratique :

Cette formule renvoie la somme correcte de 85 .

Nous pouvons confirmer que cela est correct en calculant manuellement la somme des valeurs de points pour les lignes où la colonne Position est égale à « Garde » :

Somme des points des gardes : 12 + 28 + 30 + 15 = 85 .

Ressources additionnelles

Les didacticiels suivants expliquent comment effectuer d’autres opérations courantes dans Excel :

Comment supprimer des lignes filtrées dans Excel
Comment compter les lignes filtrées dans Excel
Comment additionner les lignes filtrées dans Excel