Somme Si sur Plusieurs Feuilles – Excel et Google Sheets

Ce tutoriel montre comment utiliser les fonctions SOMMEPROD et SOMME.SI.ENS pour additionner des données répondant à certains critères sur plusieurs feuilles dans Excel et Google Sheets.

Somme Régulière sur Plusieurs Feuilles de Calcul

Il arrive que vos données s’étendent sur plusieurs feuilles de calcul dans un fichier Excel. Cette situation est courante pour les données collectées périodiquement. Chaque feuille d’un classeur peut contenir des données pour une période spécifique. Nous souhaitons créer une formule qui additionne les données contenues sur deux feuilles ou plus.

La fonction SOMME vous permet d’additionner facilement des données contenues dans plusieurs feuilles à l’aide d’une formule de type référence 3D:

=SOMME(Feuil1:Feuil2!A1)

Cependant, ceci n’est pas possible avec la fonction SOMME.SI.ENS. Pour y arriver, nous devons utiliser une formule plus complexe.

SOMME SI sur Plusieurs Feuilles

Cet exemple résume le nombre de livraisons planifiées pour chaque client sur plusieurs feuilles de calcul, chacune contenant des données relatives à un mois différent, en utilisant les fonctions SOMME.SI.ENS, SOMMEPROD et INDIRECT:

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&F3:F6&"'!"&"D3:D7");INDIRECT("'"&F3:F6&"'!"&"C3:C7");H3))

Passons en revue cette formule.

Étape 1 : Créer une Formule SOMME.SI.ENS pour une Seule Feuille de Saisie :

Nous utilisons la fonction SOMME.SI.ENS pour additionner le nombre de livraisons prévues par client pour une seule feuille de données d’entrée :

=SOMME.SI.ENS(D3:D7;C3:C7;H3)

Étape 2 : Ajout d’une Référence de Feuille à la Formule

Le résultat de la formule reste le même, mais nous spécifions que les données d’entrée se trouvent dans la feuille appelée  » Étape 2 « 

=SOMME.SI.ENS('Étape 2'!D3:D7;'Étape 2'!C3:C7;H3)

Étape 3 : Imbrication dans une Fonction SOMMEPROD

Pour préparer la formule à effectuer des calculs SOMME.SI.ENS sur plusieurs feuilles et ensuite à additionner les résultats, nous ajoutons une fonction SOMMEPROD autour de la formule

=SOMMEPROD(SOMME.SI.ENS('Étape 3'!D3:D7;'Étape 3'!C3:C7;H3))

L’utilisation de la fonction SOMME.SI.ENS sur une seule feuille donne une seule valeur. Sur plusieurs feuilles, la fonction SOMME.SI.ENS produit un tableau de valeurs (une pour chaque feuille de calcul). Nous utilisons la fonction SOMMEPROD pour totaliser les valeurs de ce tableau.

Étape 4 : Remplacer la Référence de la Feuille par une Liste de Noms de Feuille

Nous souhaitons remplacer la partie qui spécifie le Nom de la feuille dans la formule par une liste de données contenant les valeurs : Jan, Fev, Mar, Avr. Cette liste est stockée dans les cellules F3:F6.

La Fonction INDIRECT permet de s’assurer que la liste de texte indiquant les noms de feuilles est traitée comme faisant partie d’une référence de cellule valide dans la fonction SOMME.SI.ENS.

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&F3:F6&"'!"&"D3:D7");INDIRECT("'"&F3:F6&"'!"&"C3:C7");H3))

Dans cette formule, la référence de la plage écrite précédemment :

'Étape 3'!D3:D7

Est remplacée par :

INDIRECT("'"&F3:F6&"'!"&"D3:D7")

Les guillemets rendent la formule difficile à lire, elle est donc présentée ici avec des espaces ajoutés :

INDIRECT ( " ' " & F3:F6 & " ' ! " & "D3:D7" )

Cette façon de référencer une liste de cellules nous permet également de résumer les données de plusieurs feuilles qui ne suivent pas un style de liste numérique. Une référence 3D standard exigerait que les noms des feuilles soient dans le style : Input1, Input2, Input3, etc., mais l’exemple ci-dessus vous permet d’utiliser une liste de n’importe quels noms de feuilles et de les référencer dans une cellule séparée.

Verrouillage des Références de Cellule

Pour faciliter la lecture de nos formules, nous avons présenté les formules sans les références de cellule verrouillées :

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&F3:F6&"'!"&"D3:D7");INDIRECT("'"&F3:F6&"'!"&"C3:C7");H3))

Mais ces formules ne fonctionneront pas correctement lorsqu’elles seront copiées et collées ailleurs dans votre fichier. Au lieu de cela, vous devez utiliser des références de cellules verrouillées comme ceci :

=SOMMEPROD(SOMME.SI.ENS(INDIRECT("'"&$F$3:$F$6&"'!"&"D3:D7");INDIRECT("'"&$F$3:$F$6&"'!"&"C3:C7");H3))

Lisez notre article sur le verrouillage des références de cellules pour en savoir plus.

Somme Si entre Plusieurs Feuilles dans Google Sheets

L’utilisation de la fonction INDIRECT pour référencer une liste de feuilles dans une fonction SOMMEPROD et SOMME.SI.ENS n’est actuellement pas possible dans Google Sheets.

Au lieu de cela, des calculs SOMME.SI.ENS distincts peuvent être effectués pour chaque feuille d’entrée et les résultats sont additionnés :

=SOMME.SI.ENS(Jan!D3:D7;Jan!C3:C7;H3)
+SOMME.SI.ENS(Fev!D3:D7;Fev!C3:C7;H3)
+SOMME.SI.ENS(Mar!D3:D7;Mar!C3:C7;H3)
+SOMME.SI.ENS(Avr!D3:D7;Avr!C3:C7;H3)