# Maîtriser la formule SOMME SI OU dans Excel
Dans l’univers de l’analyse de données avec Excel, vous rencontrez fréquemment des situations où il faut additionner des valeurs selon plusieurs critères alternatifs. Contrairement aux conditions ET où tous les critères doivent être satisfaits simultanément, la logique OU nécessite qu’au moins un critère soit rempli. Cette distinction peut sembler subtile, mais elle transforme radicalement votre approche des formules conditionnelles. Malheureusement, Excel ne propose pas de fonction native combinant directement SOMME.SI et OU, ce qui oblige à développer des stratégies alternatives ingénieuses. La maîtrise de ces techniques vous permettra d’extraire des informations précieuses de vos tableaux de données, qu’il s’agisse d’analyses financières, de rapports de ventes ou de suivis opérationnels complexes.
Comprendre la syntaxe combinée des fonctions SOMME.SI et OU dans excel
La fonction SOMME.SI constitue l’un des outils les plus puissants d’Excel pour effectuer des calculs conditionnels. Sa syntaxe de base =SOMME.SI(plage;critère;[somme_plage]) permet d’additionner uniquement les valeurs qui répondent à une condition spécifique. Cependant, lorsque vous souhaitez appliquer plusieurs critères avec une logique OU, la situation se complique considérablement. Contrairement à ce que beaucoup pensent, vous ne pouvez pas simplement imbriquer une fonction OU à l’intérieur de SOMME.SI, car ces deux fonctions traitent les données de manières fondamentalement différentes.
Décryptage de la structure SOMME.SI avec critères multiples
La fonction SOMME.SI analyse chaque cellule individuellement dans la plage spécifiée, vérifiant si elle correspond au critère défini. Par exemple, =SOMME.SI(A2:A10;">50") additionne toutes les valeurs supérieures à 50. Cette évaluation ligne par ligne contraste avec le fonctionnement de SOMME.SI.ENS, qui vérifie simultanément plusieurs conditions sur chaque ligne. Comprendre cette différence est essentiel avant d’aborder les stratégies de logique OU. Lorsque vous travaillez avec des critères comme « additionner les ventes de la région Nord OU de la région Sud », vous devez repenser complètement votre approche structurelle.
Limitations de la fonction OU native dans les formules conditionnelles
Beaucoup d’utilisateurs tentent instinctivement d’utiliser =SOMME.SI(B2:B20;OU("Nord";"Sud");C2:C20), mais cette syntaxe génère systématiquement des erreurs ou des résultats incorrects. La fonction OU renvoie un unique résultat logique VRAI ou FAUX, alors que SOMME.SI attend un critère applicable à chaque cellule individuellement. Cette incompatibilité fondamentale explique pourquoi Excel ne peut pas évaluer correctement votre formule. La fonction OU évalue l’ensemble des arguments une seule fois, produisant un résultat global, tandis que SOMME.SI nécessite une évaluation répétée pour chaque élément de la plage.
Alternative avec SOMME.SI.ENS et opérateurs logiques
La fonction SOMME.SI.ENS, avec sa syntaxe =SOMME.SI.ENS(plage_somme;plage_critère1;critère1;plage_critère2;critère2), excelle dans la gestion de conditions ET, où tous les critères doivent être simultanément vérifiés. Pour simuler une logique OU, vous devrez malhe
… Pour simuler une logique OU, vous devrez malgré tout ruser en combinant plusieurs blocs de critères ou en jouant sur la manière dont vous construisez vos conditions. En pratique, SOMME.SI.ENS reste pensé pour les conditions cumulatives (logique ET), et non pour additionner selon des critères alternatifs. C’est pourquoi, pour une vraie formule SOMME SI OU, on se tourne souvent vers des approches hybrides : addition de plusieurs SOMME.SI.ENS, critères utilisant les caractères génériques ou encore mélange avec SOMMEPROD pour reproduire un comportement plus souple.
Utilisation de la fonction SOMMEPROD pour simuler la logique OU
La fonction SOMMEPROD est l’un des meilleurs alliés lorsqu’il s’agit de reproduire une logique OU dans Excel. Contrairement à SOMME.SI, elle travaille naturellement sur des matrices complètes et vous permet de manipuler directement des séries de VRAI/FAUX. L’idée est simple : vous transformez vos conditions en valeurs logiques, vous les combinez avec l’opérateur + (pour le OU) et vous multipliez le tout par la plage à sommer. C’est un peu comme si vous construisiez votre propre moteur de critères, cellule par cellule.
Par exemple, pour additionner les montants de la colonne C lorsque la colonne A vaut « Nord » OU « Sud », vous pouvez écrire : =SOMMEPROD(((A2:A100="Nord")+(A2:A100="Sud"))*C2:C100). Chaque parenthèse produit une matrice de 1 (VRAI) et 0 (FAUX), puis l’addition entre ces deux matrices simule la logique OU. Dès qu’au moins une des conditions est vraie sur une ligne, le résultat est non nul, et la ligne est alors prise en compte dans la somme.
Construire une formule SOMME SI avec plusieurs critères OU via l’addition
Méthode d’addition de plusieurs fonctions SOMME.SI distinctes
La première approche, la plus intuitive pour obtenir une formule SOMME SI OU, consiste à additionner plusieurs fonctions SOMME.SI. Vous écrivez simplement une SOMME.SI par critère, puis vous additionnez les résultats : chaque bloc gère un critère, et l’addition globale reproduit la logique OU. Cette méthode reste très lisible, ce qui est précieux lorsque vous partagez votre fichier avec des collègues moins à l’aise avec les formules complexes.
Reprenons l’exemple classique avec les valeurs en B2:B20 et les montants à additionner en C2:C20. Pour sommer les montants dont le critère est « toto », « tata » ou « titi », vous pouvez écrire : =SOMME.SI(B2:B20;"toto";C2:C20)+SOMME.SI(B2:B20;"tata";C2:C20)+SOMME.SI(B2:B20;"titi";C2:C20). Fonctionnellement, vous obtenez bien une somme conditionnelle OU : un enregistrement est pris en compte si son critère correspond à au moins une des valeurs listées. Cette méthode est particulièrement adaptée lorsque vous avez 2 à 4 critères, au-delà les formules deviennent rapidement difficiles à maintenir.
Gestion des plages de données non contiguës dans la formule
Il arrive fréquemment que les montants à additionner ne soient pas regroupés dans une seule plage continue. Vous devez alors gérer des plages non contiguës, réparties en plusieurs zones de votre feuille de calcul. La fonction SOMME.SI ne permet pas de spécifier plusieurs plages dans un seul appel, mais rien ne vous empêche de combiner plusieurs SOMME.SI dans une même formule, puis d’additionner leurs résultats.
Imaginons que vous ayez vos montants dans C2:C20 et dans F2:F20, toujours pour les critères en B2:B20 et E2:E20. Une solution claire consiste à écrire : =SOMME.SI(B2:B20;"toto";C2:C20)+SOMME.SI(E2:E20;"toto";F2:F20). Si vous devez gérer plusieurs critères OU, vous pouvez étendre le principe en additionnant plusieurs blocs : chaque bloc gère à la fois une zone de critères et une zone de montants. Cette approche reste un peu plus longue à écrire, mais vous conservez une formule transparente et facile à auditer.
Optimisation des références de cellules avec $ pour le verrouillage absolu
Dès que vous commencez à copier vos formules SOMME.SI vers le bas ou vers la droite, la question du verrouillage des références se pose. Pour que votre logique SOMME SI OU reste fiable, il est crucial de figer les plages de données avec le symbole $. Sans cela, vos plages se décalent lorsque vous répliquez la formule, et les résultats deviennent rapidement incohérents sans que vous vous en rendiez compte immédiatement.
Concrètement, au lieu de =SOMME.SI(B2:B20;"toto";C2:C20), privilégiez =SOMME.SI($B$2:$B$20;"toto";$C$2:$C$20). Ainsi, que vous copiez la formule sur la droite pour tester d’autres critères, ou vers le bas pour d’autres zones de synthèse, les plages de données resteront inchangées. Une bonne pratique consiste à figer systématiquement les références de base de vos tableaux (par exemple les colonnes de données sources), et à ne laisser relatifs que les éléments réellement variables (cellule contenant le critère, période, région, etc.).
Maîtriser SOMMEPROD pour des conditions OU complexes
Syntaxe SOMMEPROD avec double négation et opérateur plus
La puissance de SOMMEPROD repose sur la possibilité de manipuler directement des matrices de conditions. Pour créer une véritable formule SOMME SI OU complexe, on utilise souvent la combinaison de la double négation -- et de l’opérateur +. La double négation convertit des valeurs logiques VRAI/FAUX en 1/0, ce qui permet ensuite de faire des opérations arithmétiques sur ces conditions.
Par exemple, pour sommer les ventes de « Nord » OU « Sud » supérieures à 10 000 €, vous pouvez écrire : =SOMMEPROD(((A2:A500="Nord")+(A2:A500="Sud"))*--(C2:C500>10000)*D2:D500). Dans cette formule, (A2:A500="Nord")+(A2:A500="Sud") représente la logique OU sur la colonne région, tandis que --(C2:C500>10000) transforme la condition numérique en 1 ou 0. En multipliant le tout par la plage D2:D500, vous n’additionnez que les lignes pour lesquelles au moins un des critères texte est vrai et où le montant dépasse le seuil.
Combinaison de critères textuels et numériques avec parenthèses
Dès que vous combinez plusieurs types de critères (texte, numérique, date) dans une même formule SOMMEPROD, la clarté des parenthèses devient essentielle. Vous pouvez considérer votre formule comme une phrase logique : les parenthèses structurent cette phrase et indiquent à Excel dans quel ordre évaluer chaque partie. Sans cette rigueur, vous obtenez très vite des résultats surprenants, voire des erreurs de type #VALEUR!.
Supposons que vous vouliez sommer les montants des clients « Gold » OU « Silver » pour des factures supérieures à 5 000 € OU datées après le 1er janvier 2024. Une écriture possible serait : =SOMMEPROD(((B2:B100="Gold")+(B2:B100="Silver"))*((C2:C100>5000)+(D2:D100>DATE(2024;1;1)))*E2:E100). Notez ici l’utilisation de parenthèses pour séparer clairement les blocs de logique OU sur les statuts client et sur les seuils (montant/date). C’est un peu comme construire un schéma logique : chaque couple de parenthèses délimite une condition cohérente.
Traitement des valeurs vides et erreurs #N/A dans les calculs
Dans un monde idéal, vos tableaux seraient toujours propres. En pratique, vous devez composer avec des cellules vides, des valeurs d’erreur ou des données incohérentes. Dans une formule SOMME SI OU basée sur SOMMEPROD, ces anomalies peuvent provoquer des erreurs globales ou fausser vos résultats. Il est donc recommandé d’intégrer, autant que possible, un pré-traitement logique pour neutraliser ces cas.
Une technique courante consiste à ajouter un test sur la plage de montants ou sur la colonne clé, par exemple --ESTNUM(E2:E100) ou --(E2:E100<>""), que l’on multiplie avec les autres conditions. Par exemple : =SOMMEPROD(((B2:B100="Nord")+(B2:B100="Sud"))*--ESTNUM(E2:E100)*E2:E100) garantit que seules les lignes contenant un nombre valide dans la colonne montants sont prises en compte. Si des erreurs #N/A sont possibles (par exemple après des recherches VLOOKUP), il peut être judicieux de les traiter en amont avec SIERREUR dans une colonne intermédiaire, plutôt que de complexifier à l’excès la formule SOMMEPROD.
Comparaison des performances SOMMEPROD versus SOMME.SI multiple
Sur des bases de données de quelques centaines de lignes, la différence de performance entre plusieurs SOMME.SI et une unique formule SOMMEPROD est souvent négligeable. En revanche, dès que l’on dépasse plusieurs dizaines de milliers de lignes, et que les formules sont répliquées sur de nombreuses cellules, l’impact sur les temps de calcul devient sensible. SOMMEPROD, qui évalue systématiquement des matrices complètes, peut devenir plus coûteux qu’une série de SOMME.SI correctement ciblées.
Une règle pratique consiste à privilégier les SOMME.SI ou SOMME.SI.ENS additionnés lorsque vos critères sont simples et peu nombreux, et à basculer sur SOMMEPROD dès que la logique OU devient vraiment complexe (combinaison de nombreux critères, interactions entre colonnes, conditions imbriquées). N’hésitez pas à tester les deux approches sur un échantillon de votre fichier : sur certains modèles, une unique formule SOMMEPROD bien écrite est plus rapide et plus facile à maintenir qu’une mosaïque de SOMME.SI dispersées.
Implémenter des critères OU avec des fonctions matricielles dynamiques
Exploitation de FILTER et SUM dans excel 365 pour la logique OU
Si vous utilisez Excel 365 ou Excel 2021, vous disposez d’un atout majeur : les fonctions matricielles dynamiques, notamment FILTER et SUM. Elles permettent d’implémenter une logique SOMME SI OU de manière très lisible, en deux temps : on filtre d’abord les lignes qui respectent au moins un critère, puis on additionne les montants correspondants. C’est un peu comme si vous construisiez un sous-tableau temporaire, uniquement avec les lignes pertinentes, avant d’en faire le total.
Par exemple, pour sommer les montants de la colonne D lorsque la région est « Nord » OU « Sud », vous pouvez écrire : =SOMME(FILTER(D2:D500;(B2:B500="Nord")+(B2:B500="Sud"))). La fonction FILTER renvoie un tableau contenant uniquement les montants répondant aux critères, et SUM réalise ensuite la somme. Cette approche est très expressive : même un utilisateur peu expérimenté comprend facilement que l’on filtre d’abord, puis que l’on additionne.
Utilisation de l’opérateur + dans les arguments de FILTER
Comme pour SOMMEPROD, l’opérateur + est la clé pour simuler une logique OU à l’intérieur de FILTER. Les arguments de critère de FILTER acceptent des matrices de VRAI/FAUX. Lorsque vous ajoutez plusieurs matrices entre elles, vous obtenez une nouvelle matrice dans laquelle les lignes correspondant à au moins un des critères sont marquées comme vraies (valeur non nulle). C’est exactement ce qui vous permet d’implémenter un SOMME SI OU moderne.
Imaginons que vous souhaitiez filtrer toutes les lignes dont la catégorie est « Produit A » OU « Produit B » OU dont le montant dépasse 10 000 €. Vous pourriez écrire : =FILTER(A2:E100;(C2:C100="Produit A")+(C2:C100="Produit B")+(D2:D100>10000)). FILTER renverra alors uniquement les lignes qui vérifient au moins l’un de ces critères. Vous pouvez ensuite enchaîner avec SUM, SUMIFS ou d’autres fonctions pour affiner encore votre analyse.
Gestion des tableaux structurés et références dynamiques
Pour des modèles professionnels amenés à évoluer, l’utilisation des tableaux structurés (ListObjects) combinés aux fonctions dynamiques est particulièrement efficace. Lorsque vous convertissez votre plage de données en tableau (via Insertion > Tableau), les références deviennent dynamiques et s’ajustent automatiquement aux nouvelles lignes ajoutées. Vos formules SOMME SI OU basées sur FILTER et SUM restent ainsi valides sans modification manuelle.
Par exemple, si votre tableau s’appelle Ventes avec les colonnes [Région] et [Montant], vous pouvez écrire : =SOMME(FILTER(Ventes[Montant];(Ventes[Région]="Nord")+(Ventes[Région]="Sud"))). Dès que vous ajoutez une nouvelle ligne à la fin du tableau, la formule prend automatiquement en compte ces nouvelles données. C’est une approche idéale pour les rapports périodiques (hebdomadaires, mensuels) où les données sont régulièrement enrichies.
Cas pratiques et exemples concrets de SOMME SI OU
Calcul de chiffre d’affaires multi-régions avec critères géographiques
Dans un contexte commercial, il est fréquent de devoir analyser le chiffre d’affaires cumulé sur plusieurs régions à la fois. Par exemple, vous pouvez vouloir suivre un indicateur global « Sud Europe » qui regroupe les régions « France », « Espagne » et « Italie ». Une formule SOMME SI OU vous permet de regrouper ces zones sans modifier votre modèle de données ni créer de colonnes intermédiaires.
Avec SOMMEPROD, une formule type pourrait être : =SOMMEPROD(((B2:B500="France")+(B2:B500="Espagne")+(B2:B500="Italie"))*C2:C500), où B contient la région et C le montant des ventes. Avec Excel 365, une alternative encore plus lisible est : =SOMME(FILTER(C2:C500;(B2:B500="France")+(B2:B500="Espagne")+(B2:B500="Italie"))). Dans les deux cas, vous obtenez un chiffre d’affaires consolidé multi-régions sans avoir à redessiner votre tableau croisé dynamique à chaque fois.
Agrégation de données par catégories de produits alternatives
Autre scénario courant : regrouper plusieurs catégories de produits dans une même analyse. Peut-être souhaitez-vous suivre une famille « Premium » qui regroupe les catégories « Luxe », « Edition limitée » et « Signature ». Plutôt que de modifier les codes produits, vous pouvez utiliser une formule SOMME SI OU pour agréger ces catégories à la volée.
Une formule SOMME.SI multiple pourrait ressembler à : =SOMME.SI($B$2:$B$1000;"Luxe";$D$2:$D$1000)+SOMME.SI($B$2:$B$1000;"Edition limitée";$D$2:$D$1000)+SOMME.SI($B$2:$B$1000;"Signature";$D$2:$D$1000). Si vous avez de nombreux regroupements, vous pouvez envisager une approche plus générique avec SOMMEPROD, par exemple en utilisant une plage de critères nommée CatPremium : =SOMMEPROD(ISNUMBER(EQUIV(B2:B1000;CatPremium;0))*D2:D1000). Cette approche reste très flexible : il vous suffit de modifier la liste CatPremium pour ajuster le périmètre de votre famille de produits.
Sommation conditionnelle sur plusieurs statuts de commandes
Dans la gestion des opérations, on analyse souvent les volumes par statut de commande : « En cours », « Validée », « Expédiée », « Annulée », etc. Vous pouvez vouloir suivre d’un seul coup d’œil le montant total des commandes « En cours » OU « Validée », considérées comme pipeline ferme. Une formule SOMME SI OU répond parfaitement à ce type de besoin.
Avec SOMMEPROD, vous pouvez écrire : =SOMMEPROD(((C2:C1000="En cours")+(C2:C1000="Validée"))*E2:E1000), où C contient le statut et E le montant de la commande. Si vous utilisez Excel 365, une variante très lisible serait : =SOMME(FILTER(E2:E1000;(C2:C1000="En cours")+(C2:C1000="Validée"))). Cette logique est très utile pour des tableaux de bord opérationnels qui segmentent les commandes selon plusieurs états à suivre simultanément.
Analyse financière avec critères de départements ou de périodes
En finance, les besoins d’agrégation conditionnelle sont nombreux : suivre les dépenses d’un groupe de départements, consolider les charges sur plusieurs périodes non consécutives, ou encore analyser un ensemble de centres de coûts en parallèle. La formule SOMME SI OU devient alors un véritable couteau suisse pour vos reportings mensuels et annuels.
Par exemple, pour sommer les charges des départements 101, 103 et 205 sur l’exercice 2024, vous pouvez combiner plusieurs critères avec SOMMEPROD : =SOMMEPROD(((A2:A500=101)+(A2:A500=103)+(A2:A500=205))*--(B2:B500>=DATE(2024;1;1))*--(B2:B500<DATE(2025;1;1))*C2:C500). Vous obtenez ainsi une agrégation très précise, correspondant à une logique « département 101 OU 103 OU 205 » ET « date dans l’année 2024 ». Une autre approche consiste à mettre les numéros de départements ciblés dans une petite table de paramétrage, puis à utiliser EQUIV/ISNUMBER dans SOMMEPROD pour rendre votre formule entièrement pilotée par paramètres.
Débogage et optimisation des formules SOMME SI OU avancées
Résolution des erreurs #VALEUR et #REF dans les formules complexes
Plus vos formules SOMME SI OU deviennent sophistiquées, plus le risque de rencontrer des erreurs de type #VALEUR! ou #REF! augmente. Ces erreurs proviennent souvent de plages de tailles différentes, de références supprimées, ou de combinaisons illégales entre valeurs texte et numériques. Une première étape consiste à vérifier systématiquement que toutes vos plages matricielles ont exactement le même nombre de lignes (et de colonnes, si nécessaire).
En cas d’erreur persistante, décomposez votre formule en plusieurs morceaux dans des cellules intermédiaires : testez par exemple uniquement la partie conditionnelle (A2:A100="Nord")+(A2:A100="Sud"), puis la partie numérique --(C2:C100>10000). Vous pouvez aussi utiliser SIERREUR pour neutraliser temporairement les erreurs et confirmer que le problème vient bien d’une sous-partie de la formule. Cette démarche pas-à-pas, même si elle peut paraître un peu fastidieuse, vous fait gagner beaucoup de temps sur des modèles complexes.
Techniques d’audit avec la fenêtre évaluation de formule
La fenêtre Évaluation de formule (disponible via l’onglet Formules > Évaluer la formule) est un outil précieux pour comprendre ce que fait réellement une formule SOMME SI OU. Elle vous permet d’avancer étape par étape dans le calcul, en visualisant comment Excel remplace progressivement chaque sous-expression par son résultat. C’est un peu comme mettre votre formule au ralenti pour observer ses rouages internes.
Pour auditer une formule SOMMEPROD ou FILTER complexe, placez le curseur sur la cellule concernée, ouvrez la fenêtre d’évaluation, puis utilisez le bouton Évaluer pour dérouler la logique. Vous verrez apparaître les matrices de VRAI/FAUX, les tableaux de nombres, et pourrez ainsi repérer à quel moment une erreur surgit. Combinée aux repères visuels (coloration des plages dans la barre de formule), cette technique d’audit simplifie grandement le débogage, même sur des fichiers comportant plusieurs milliers de lignes.
Amélioration des temps de calcul sur grandes bases de données
Lorsque vos fichiers dépassent quelques dizaines de milliers de lignes, les formules SOMME SI OU mal optimisées peuvent devenir un véritable frein à la productivité. Quelques bons réflexes permettent néanmoins de conserver des temps de calcul raisonnables. Le premier est de limiter la taille des plages : évitez les références de type A:A ou 2:2 lorsque vos données ne couvrent que les lignes 2 à 5000.
Ensuite, privilégiez, lorsque c’est possible, une seule formule SOMMEPROD bien optimisée ou un bloc FILTER+SUM, plutôt que de multiples SOMME.SI dispersées dans tout le classeur. Si votre modèle le permet, regroupez les calculs lourds dans une feuille dédiée, et utilisez des tableaux structurés pour limiter les recalculs à la zone réellement utilisée. Enfin, n’hésitez pas à tester le mode de calcul manuel (onglet Formules > Options de calcul) lorsque vous travaillez sur des ajustements complexes : vous garderez ainsi la main sur le déclenchement des recalculs tout en continuant à affiner votre logique SOMME SI OU.