SOMMEPROD : la formule la plus puissante d'Excel expliquée

SOMMEPROD : la formule la plus puissante d'Excel expliquée

La plupart des utilisateurs d'Excel connaissent SOMME.SI ou NB.SI.ENS. Mais peu maîtrisent réellement SOMMEPROD, cette formule discrète qui surclasse pratiquement toutes les autres en termes de flexibilité et de puissance. Après dix ans sur des projets industriels complexes, je peux affirmer que SOMMEPROD est la solution à 80% des problèmes de calculs conditionnels avancés.

Pourquoi SOMMEPROD est différent

Contrairement aux formules conditionnelles classiques, SOMMEPROD ne se limite pas à appliquer des critères simples. Son principe : multiplier des tableaux entre eux, élément par élément, puis additionner le résultat. Cette mécanique permet de créer des conditions booléennes sophistiquées sans jamais saisir de formule matricielle avec Ctrl+Maj+Entrée.

La vraie force de SOMMEPROD réside dans sa capacité à transformer des conditions logiques en valeurs numériques. Quand vous écrivez (A2:A100="Paris"), Excel génère un tableau de VRAI/FAUX. SOMMEPROD convertit automatiquement ces valeurs en 1 et 0, puis effectue ses calculs. Cette conversion implicite ouvre des possibilités infinies.

Syntaxe et logique de base

La syntaxe est trompeusement simple :

=SOMMEPROD(tableau1; [tableau2]; [tableau3]; ...)

Sans condition, =SOMMEPROD(B2:B100) additionne simplement les valeurs. L'intérêt surgit avec les conditions multiples :

=SOMMEPROD((A2:A100="Paris")*(B2:B100="Ventes")*(C2:C100))

Décortiquons : chaque condition entre parenthèses génère un tableau de 1 et 0. La multiplication * combine ces tableaux. Seules les lignes où TOUTES les conditions sont vraies (1×1=1) conservent leur valeur de C2:C100. Les autres deviennent 0 (1×0=0). SOMMEPROD additionne ensuite le résultat.

C'est un ET logique implicite. Pour un OU logique, utilisez l'addition :

=SOMMEPROD(((A2:A100="Paris")+(A2:A100="Lyon"))*(C2:C100))

Cas d'usage concrets pour l'industrie

Comptage avec critères complexes : compter les lignes où le montant dépasse 1000 ET la date est en 2024 :

=SOMMEPROD((C2:C100>1000)*(ANNEE(D2:D100)=2024))

Somme avec exclusions : additionner les ventes sauf Paris et Lyon :

=SOMMEPROD((A2:A100<>"Paris")*(A2:A100<>"Lyon")*(B2:B100))

Calculs sur texte partiel : somme des montants pour les références contenant "PROD" :

=SOMMEPROD(ESTNUM(CHERCHE("PROD";A2:A100))*B2:B100)

CHERCHE retourne un numéro de position ou une erreur. ESTNUM filtre uniquement les correspondances valides.

Agrégation croisée : calculer le total des ventes par combinaison produit/région sans tableau croisé dynamique :

=SOMMEPROD((A2:A100=E2)*(B2:B100=F2)*(C2:C100))

Où E2 contient le produit recherché et F2 la région. Répliquez cette formule sur une matrice de résultats.

Avantages sur les autres formules

SOMME.SI.ENS impose des critères de type "égal à" ou simples. SOMMEPROD accepte N'IMPORTE QUELLE expression logique : fonctions texte, calculs, conditions imbriquées. Vous pouvez même comparer deux colonnes entre elles :

=SOMMEPROD((A2:A100=B2:B100)*(C2:C100))

Somme les montants uniquement quand colonne A égale colonne B sur la même ligne. Impossible avec SOMME.SI.ENS.

Autre avantage : pas de formule matricielle héritée. Les anciennes formules matricielles ralentissent les fichiers et déroutent les utilisateurs. SOMMEPROD offre la même puissance sans cette complexité.

Pièges à éviter

Performance sur grands volumes : SOMMEPROD recalcule tout le tableau à chaque modification. Sur 100 000 lignes avec cinq conditions, le calcul devient perceptible. Dans ce cas, basculez vers Power Query ou des tableaux croisés dynamiques.

Gestion des erreurs : si une cellule contient #N/A ou #DIV/0!, SOMMEPROD retourne une erreur. Protégez avec SIERREUR ou nettoyez vos données en amont.

Oubli des parenthèses : chaque condition DOIT être entre parenthèses. =SOMMEPROD(A2:A100="Paris"*B2:B100) ne fonctionne pas. Écrivez =SOMMEPROD((A2:A100="Paris")*B2:B100).

Confusion avec SOMME : =SOMMEPROD(A2:A10;B2:B10) additionne séparément les deux plages puis les multiplie (résultat inattendu). Pour multiplier élément par élément puis additionner : =SOMMEPROD(A2:A10*B2:B10).

Quand passer à autre chose

SOMMEPROD atteint ses limites sur les tableaux dépassant 50 000 lignes avec recalculs fréquents, ou quand vous cumulez plus de sept conditions. À ce stade, les solutions VBA avec tableaux en mémoire ou Power Query deviennent plus pertinentes. Mais pour 90% des besoins métiers quotidiens, SOMMEPROD reste imbattable en rapport efficacité/simplicité.

Conclusion

SOMMEPROD transforme Excel en véritable moteur de calcul conditionnel. Sa logique booléenne masquée et sa flexibilité totale en font l'outil de prédilection pour les analyses complexes sans VBA. Maîtrisez cette formule, et vous résoudrez des problèmes que vos collègues croient impossibles sans macros.