Comment utiliser SUBTOTAL avec SUMPRODUCT dans Excel

Vous pouvez utiliser la formule suivante pour combiner les fonctions SUBTOTAL et SUMPRODUCT dans Excel :

=SUMPRODUCT(C2:C11,SUBTOTAL(9,OFFSET(D2:D11,ROW(D2:D11)-MIN(ROW(D2:D11)),0,1)))

Cette formule particulière vous permet de sommer le produit des valeurs de la plage C2:C11 et de la plage D2:D11 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 SUMPRODUCT dans Excel

Supposons que nous disposions de l’ensemble de données suivant contenant des informations sur les ventes de divers produits dans deux épiceries différentes :

Filtrons ensuite les données pour afficher uniquement les lignes dont la valeur dans la colonne Store est B .

Pour ce faire, mettez en surbrillance la plage de cellules A1:D11 . 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 Store et assurez-vous que seule la case à côté de B est cochée, puis cliquez sur OK :

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

Si nous essayons d’utiliser la fonction SUMRPODUCT() pour additionner le produit des valeurs des colonnes Ventes et Prix, elle renverra en fait la somme du produit de ces deux colonnes dans l’ensemble de données d’origine :

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

=SUMPRODUCT(C2:C11,SUBTOTAL(9,OFFSET(D2:D11,ROW(D2:D11)-MIN(ROW(D2:D11)),0,1)))

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

Cette formule renvoie la somme correcte de 77,5 .

Nous pouvons confirmer que cela est correct en calculant manuellement la somme du produit des valeurs entre les colonnes Ventes et Prix :

Somme du produit des valeurs entre les ventes et le prix : (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

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