Faire une SOMME en ignorant les erreurs dans Excel

Une seule cellule contenant #N/A, #DIV/0! ou #VALEUR! suffit à rendre une SOMME entière inutilisable. Excel propose plusieurs méthodes pour contourner ce problème et obtenir la somme des cellules valides, selon votre version et la source des erreurs.

Le problème

Situation type : vous avez une colonne de montants calculés avec des formules RECHERCHEV ou des divisions. Certaines lignes renvoient des erreurs (#N/A, #DIV/0!…). Votre SOMME en bas de colonne affiche elle aussi une erreur.

C2  = 1 500 €
C3  = #N/A       ← RECHERCHEV n'a pas trouvé la valeur
C4  = 800 €
C5  = #DIV/0!    ← division par zéro
C6  = 2 100 €
=SOMME(C2:C6)  →  #N/A   ← toute la somme est compromise

Objectif : obtenir 4 400 € (1 500 + 800 + 2 100) en ignorant les erreurs.

Méthode 1 — AGREGAT (recommandée)

1AGREGAT avec option "ignorer les erreurs"

Recommandée Excel 2010+

=AGREGAT(9; 6; C2:C6)
     ↑       ↑
     9=SOMME  6=ignorer les erreurs

C'est la méthode la plus propre. AGREGAT est conçu précisément pour ce cas : la valeur 6 comme deuxième argument lui demande d'ignorer toutes les cellules en erreur.

=AGREGAT(9;6;C2:C100)      ← SOMME en ignorant les erreurs
=AGREGAT(1;6;C2:C100)      ← MOYENNE en ignorant les erreurs
=AGREGAT(4;6;C2:C100)      ← MAX en ignorant les erreurs
=AGREGAT(5;6;C2:C100)      ← MIN en ignorant les erreurs
=AGREGAT(2;6;C2:C100)      ← NB en ignorant les erreurs

Utilisez =AGREGAT(9;7;C2:C100) (option 7) pour ignorer à la fois les erreurs ET les lignes masquées/filtrées simultanément.

Méthode 2 — SOMMEPROD + SIERREUR

2SOMMEPROD avec SIERREUR pour remplacer les erreurs par 0

Compatible toutes versions récentes Excel 2007+

=SOMMEPROD(SIERREUR(C2:C6;0))

SIERREUR appliqué sur une plage renvoie un tableau où chaque erreur est remplacée par 0. SOMMEPROD additionne ce tableau.

Décomposé :
SIERREUR(C2:C6;0)  →  {1500; 0; 800; 0; 2100}
SOMMEPROD(...)     →  4 400 ✓

— Variantes utiles :
=SOMMEPROD(SIERREUR(C2:C100;0))              ← ignorer toutes les erreurs
=SOMMEPROD(SI.NON.DISP(C2:C100;0))          ← ignorer uniquement #N/A
=SOMMEPROD(SIERREUR(C2:C100;0)*(A2:A100="Paris"))  ← avec condition en plus

Méthode 3 — Corriger la source des erreurs

3Envelopper chaque formule source dans SIERREUR

Meilleure pratique Excel 2007+

Au lieu de traiter le symptôme (la SOMME), corrigez la cause en faisant en sorte que chaque cellule renvoie 0 ou vide au lieu d'une erreur :

— Avant (génère des erreurs) :
C2 = =RECHERCHEV(A2;Tarifs;2;0)       ← renvoie #N/A si non trouvé
C3 = =B3/D3                            ← renvoie #DIV/0! si D3=0

— Après (erreurs remplacées par 0) :
C2 = =SIERREUR(RECHERCHEV(A2;Tarifs;2;0);0)
C3 = =SIERREUR(B3/D3;0)
   ou
C3 = =SI(D3=0;0;B3/D3)                ← plus explicite

— SOMME normale fonctionne à nouveau :
=SOMME(C2:C6)  →  4 400 ✓

Remplacer une erreur par 0 peut fausser une moyenne. Si vous calculez aussi =MOYENNE(C2:C6), les zéros parasiteront le calcul. Dans ce cas, préférez AGREGAT(1;6;C2:C6) pour la moyenne.

Méthode 4 — Pour Excel 2003 et antérieur

4Formule matricielle avec ESTERREUR

Compatibilité maximale Toutes versions

Formule matricielle (valider avec Ctrl+Maj+Entrée) :
=SOMME(SI(ESTERREUR(C2:C6);0;C2:C6))

Ou avec SOMMEPROD (pas besoin de Ctrl+Maj+Entrée) :
=SOMMEPROD((ESTERREUR(C2:C6)=FAUX)*C2:C6)
=SOMMEPROD(NON(ESTERREUR(C2:C6))*C2:C6)

Ces formules utilisent ESTERREUR pour créer un masque booléen et n'additionner que les cellules sans erreur.

Comparaison des méthodes

Méthode	Formule	Version min.	Vitesse	Lisibilité
AGREGAT	`=AGREGAT(9;6;C2:C100)`	Excel 2010	Très rapide	Excellente
SOMMEPROD+SIERREUR	`=SOMMEPROD(SIERREUR(C2:C100;0))`	Excel 2007	Rapide	Bonne
Correction à la source	SIERREUR dans chaque cellule	Excel 2007	Très rapide	Meilleure
Formule matricielle	`{=SOMME(SI(ESTERREUR(...)))}`	Toutes	Lente sur grands tableaux	Moyenne

Cas spéciaux : erreurs #N/A uniquement

— Si les erreurs sont uniquement des #N/A (RECHERCHEV non trouvé) :
=SOMMEPROD(SI.NON.DISP(C2:C100;0))

— Pourquoi c'est mieux que SIERREUR dans ce cas :
  SI.NON.DISP laisse passer les vrais #DIV/0! ou #VALEUR! → vous détectez d'autres bugs
  SIERREUR masque tout → vous pourriez passer à côté d'un vrai problème

Combiner : somme sans erreurs ET avec conditions

— Somme des montants de "Paris" en ignorant les erreurs :
=SOMMEPROD(SIERREUR(C2:C100;0)*(A2:A100="Paris"))

— Avec AGREGAT (pas possible directement — AGREGAT ne supporte pas les critères)
— Dans ce cas, SOMMEPROD+SIERREUR est la bonne approche

— Ignorer erreurs ET lignes masquées ET appliquer un critère :
→ Pas de formule unique : filtrez d'abord, utilisez AGREGAT(9;7;...) pour le total global
   puis SOMMEPROD+SIERREUR pour le total conditionnel

Exemple complet : rapport de ventes avec RECHERCHEV

Contexte : colonne C = RECHERCHEV(B2;Catalogue;3;0) pour récupérer le prix unitaire
           colonne D = quantité
           colonne E = C*D (montant, peut hériter du #N/A de C)

— E2 sans protection :
=C2*D2    → #N/A si C2=#N/A

— E2 avec protection à la source :
=SIERREUR(C2;0)*D2

— Total en bas de colonne (E102) :
=SOMME(E2:E101)           ← fonctionne si E2 est protégé
=AGREGAT(9;6;E2:E101)     ← fonctionne même sans protection individuelle

Bonne pratique : protégez à la source (SIERREUR dans chaque formule de calcul) ET utilisez AGREGAT pour les totaux. Vous bénéficiez à la fois d'une colonne propre pour la lisibilité et d'un total robuste contre d'éventuelles erreurs résiduelles.

SOMME vs SOUS.TOTAL vs AGREGAT → SIERREUR vs SI.NON.DISP Fiche AGREGAT