Additionner les cellules contenant une formule

Résumé

Pour additionner uniquement les cellules contenant une formule, on peut utiliser la fonction SOMMEPROD associée à la fonction ESTFORMULE.

= SOMMEPROD( ESTFORMULE(C3:C10)*C3:C10 )

Si vous possédez une version d’Excel antérieure à Office 365, il faudra valider la formule grâce à MAJ+CTRL+ENTREE.

Formule Excel – Additionner les cellules contenant une formule

= SOMMEPROD( ESTFORMULE(plage_cellules)*plage_cellules )

Explications

Dans l’exemple, l’objectif est d’additionner uniquement les cellules qui sont issues de l’utilisation d’une formule. Dans certains fichiers, nous voulons identifier aisément le volume des données issues de l’utilisation de formules et fonctions, des données saisies en « dur ».

La fonction ESTFORMULE vérifie la valeur indiquée entre parenthèses et renvoie VRAI si elle commence par « = » et FAUX dans le cas contraire.

= ESTFORMULE( valeur_ou_cellule )

= ESTFORMULE( C4 )
B2 contient '=650+5842'
--> Résultat : VRAI

= ESTFORMULE( C3 )
B6 contient '1234'
--> Résultat : FAUX

En effectuant cette opération sur une plage de cellules on obtient une liste de valeurs.

= ESTFORMULE( C3:C10 )

= {FAUX;VRAI;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI}

Fonction SOMMEPROD

La fonction SOMMEPROD permet de multiplier des matrices entre elles.

= SOMMEPROD( A1:A3 ; B1:B3 )
--> Résultat : A1xB1 + A2xB2 + A3xB3

Dans notre cas, notre première matrice est la liste des VRAI/FAUX précédemment obtenue. La seconde matrice sera les valeurs présentes dans les cellules. Pour Excel, multiplier VRAI par un valeur revient à faire ‘1 x valeur’. Pour FAUX, cela revient à faire ‘0 x valeur’.

= SOMMEPROD( ESTFORMULE( C3:C10 ) ; C3:C10 )

= SOMMEPROD( {FAUX;VRAI;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI} ;
             {1234;6492;583;391;564;285;810;50} )

= 0 x 1234 + 1 x 64992 + 0 x 583 + 1 x 391 + 0 x 564 + 0 x 285 + 1 x 810 + 1 x 50

= 7743

Pour rappel, pour les versions d’Excel antérieures à Office 365, il faudra valider la formule en tapant MAJ+CTRL+ENTREE.

Formule inverse – Additionner les cellules ne contenant pas une formule

Il est possible d’obtenir le résultat inverse et ainsi additionner les cellules ne contenant pas de formule. Pour cela, il faudra utiliser la fonction NON. Cette fonction permet de transformer les VRAI en FAUX et inversement.

La formule sera alors du type :

= SOMMEPROD( NON( ESTFORMULE(plage_cellules) ) *plage_cellules )

On obtiendra alors :

= SOMMEPROD( NON( ESTFORMULE( C3:C10 ) ) ; C3:C10 )

= SOMMEPROD( NON( {FAUX;VRAI;FAUX;VRAI;FAUX;FAUX;VRAI;VRAI} ) ;
             {1234;6492;583;391;564;285;810;50} )

= SOMMEPROD( {VRAI;FAUX;VRAI;FAUX;VRAI;VRAI;FAUX;FAUX} ) ;
             {1234;6492;583;391;564;285;810;50} )

= 1 x 1234 + 0 x 64992 + 1 x 583 + 0 x 391 + 1 x 564 + 1 x 285 + 0 x 810 + 0 x 50

= 2666

Formules en lien

• Compter le nombre de cellules contenant une formule
• SOMME.SI plusieurs critères
• Compter le nombre de cellules égales à un texte
• Compter le nombre de cellules contenant une partie du texte (mot, caractère)
• Compter nombre de valeurs texte différentes
• Compter le nombre de cellules non vides
• NB.SI avec plusieurs critères
• Somme si différent de
• Somme si contient