Qu'est-ce que la fonction SOUS.
SOUS.TOTAL (SUBTOTAL en anglais) t'évite de recalculer un total à la main à chaque changement de filtre, ou pire, de livrer un chiffre faux parce qu'une ligne masquée traîne encore dans le calcul. Tu la choisis dès qu'un tableau de bord doit rester juste quel que soit le filtre actif, sans jamais retoucher la formule.
Concrètement, c'est lui qui fait que ton tableau de bord affiche le CA de la région sélectionnée, que ta moyenne de factures se recalcule au fur et à mesure que tu filtres par statut, et que ton compteur de projets actifs reste juste sans jamais toucher à la formule. Fini les totaux faux parce qu'un filtre était actif.
Syntaxe
=SOUS.TOTAL(no_fonction; réf1; [réf2]; ...)Clique sur un argument pour aller à son explication.
Les codes 1-11 ignorent les lignes filtrées mais incluent les lignes masquées manuellement. Les codes 101-111 ignorent toutes les lignes masquées, quelle que soit la méthode. Dans la pratique, préfère les codes 101-111.
Comprendre chaque paramètre de la fonction SOUS.TOTAL
SOUS.TOTAL démarre par un argument que les autres fonctions de calcul n'ont pas : un code chiffré qui dit quel calcul faire (somme, moyenne, comptage…). Viennent ensuite les plages, la première étant obligatoire. Tu peux en empiler jusqu'à 253, mais c'est bien ce code en tête qui change tout le comportement de la formule.
no_fonction
: c'est le code qui détermine le type de calcul à effectuerTu as le choix entre 11 fonctions différentes, chacune disponible en deux versions.
Les codes 1-11 ignorent les lignes masquées par un filtre, mais incluent les lignes masquées manuellement (clic droit > Masquer). Les codes 101-111 ignorent toutes les lignes masquées, quelle que soit la méthode.
Codes les plus courants : 9 ou 109 = SOMME, 1 ou 101 = MOYENNE, 2 ou 102 = NB (compte les nombres), 3 ou 103 = NBVAL (compte les cellules non vides), 4 ou 104 = MAX, 5 ou 105 = MIN.
réf1
: c'est la première plage de cellules sur laquelle effectuer le calculÇa peut être une référence simple comme B2:B50 ou une colonne entière comme B:B. C'est le seul paramètre de plage obligatoire.
[réf2], [réf3], ...
: tu peux ajouter jusqu'à 253 plages supplémentaires(facultatif)Pratique si tes données sont réparties dans plusieurs colonnes non adjacentes. Sépare chaque plage par un point-virgule.
Exemples pratiques pas à pas
Comment additionner les lignes filtrées avec la fonction SOUS.TOTAL
La revue de budget est lundi et ton chef veut voir les dépenses tantôt de tous les départements, tantôt d'un seul. Ton tableau liste chaque ligne de dépense avec son département, son responsable et son type, et il te faut un total qui suive le filtre au lieu de rester figé. La fonction SOUS.TOTAL calcule ce total sur les seules lignes affichées, là où la fonction SOMME additionnerait tout, filtre ou pas.
- 1Dans une cellule, écris
=SOUS.TOTAL(. - 2En 1er argument : saisis le code du calcul à faire,
9pour une somme. C'est ce premier chiffre, absent des autres fonctions de calcul, qui décide de l'opération, et tu le remplaceras pour obtenir une moyenne ou un comptage. - 3En 2ᵉ argument : sélectionne la plage à additionner, la colonne Dépenses en entier sans son en-tête,
D2:D19. La fonction ne totalise que les lignes encore affichées, si bien que le résultat suit chaque filtre. - 4Ferme la parenthèse et appuie sur Entrée.
=SOUS.TOTAL(9; D2:D19)9 en tête qui fait la différence : dès que tu filtres le tableau sur un département, le total se refait sur les seules lignes encore affichées, sans que tu retouches quoi que ce soit.
Comment calculer la moyenne des lignes visibles avec la fonction SOUS.TOTAL
Un client conteste sa facture et tu veux savoir, avant de le rappeler, à combien tourne le montant moyen de celles qui sont déjà réglées. Tes factures sont listées avec leur client, leur statut et leur montant, et tu veux une moyenne qui se recalcule selon le statut affiché. La fonction SOUS.TOTAL propose onze calculs différents, et tu passes de l'un à l'autre en changeant un seul chiffre.
- 1Dans une cellule, écris
=SOUS.TOTAL(. - 2En 1er argument : saisis le code du calcul,
1pour une moyenne là où9demandait une somme. C'est le seul argument à changer pour passer d'un calcul à l'autre,2comptant les nombres et4sortant le maximum. - 3En 2ᵉ argument : sélectionne la plage à analyser, la colonne Montant,
D2:D7. La fonction fait la moyenne des seuls montants encore visibles et repart d'un calcul neuf à chaque changement de filtre. - 4Ferme la parenthèse et appuie sur Entrée.
=SOUS.TOTAL(1; D2:D7)1 demande une moyenne. Sans filtre, la fonction divise les 12 000 € du tableau par 6 factures et affiche 2 000 €. Filtre sur le statut « Payée » et le calcul repart sur les 4 factures restantes (1 900 €), alors que la fonction MOYENNE resterait bloquée à 2 000 €.
Comment ignorer les lignes masquées à la main avec la fonction SOUS.TOTAL
Ton reporting part dans l'heure et tu as masqué une ligne que ton chef n'a pas à voir dans cette version. Ton tableau donne le chiffre d'affaires par région et par commercial, et le total du bas doit refléter ce qui reste à l'écran, pas ce qui dort encore dans le fichier. La fonction SOUS.TOTAL propose chaque calcul en deux versions, et seule la seconde tient compte des lignes que tu masques toi-même.
- 1Dans une cellule, écris
=SOUS.TOTAL(. - 2En 1er argument : saisis le code du calcul,
109plutôt que9. Les deux lancent une somme, mais la version à trois chiffres ignore aussi les lignes masquées à la main, pas seulement celles qu'un filtre écarte. - 3En 2ᵉ argument : sélectionne la plage à additionner, la colonne CA,
C2:C7. Chaque code de la famille 1-11 a son jumeau en 101-111, si bien que1devient101pour la moyenne et3devient103pour le comptage. - 4Ferme la parenthèse et appuie sur Entrée.
=SOUS.TOTAL(109; C2:C7)109 écarte aussi les lignes cachées à la main. Avec 9, le même geste laisserait 560 000 € à l'écran sans que personne comprenne pourquoi.
Comment cumuler des sous-totaux sans double comptage avec la fonction SOUS.TOTAL
Ton budget est découpé par département avec un sous-total sous chaque bloc, et il te faut le total général avant l'arbitrage de vendredi. Chaque poste a son montant, chaque département son sous-total, et tu veux additionner la colonne entière sans compter deux fois ce qui est déjà cumulé. La fonction SOUS.TOTAL reconnaît ses propres résultats dans la plage et les saute toute seule, là où la fonction SOMME les additionnerait comme n'importe quel montant.
- 1Dans une cellule, écris
=SOUS.TOTAL(. - 2En 1er argument : saisis le code du calcul,
9pour une somme ordinaire. C'est la fonction elle-même qui se charge d'écarter les sous-totaux déjà présents dans la plage. - 3En 2ᵉ argument : sélectionne toute la colonne Montant, lignes de sous-totaux comprises,
C2:C7. La fonction saute les cellules qui contiennent déjà un SOUS.TOTAL, à condition que tes sous-totaux soient écrits avec elle et non avec la fonction SOMME. - 4Ferme la parenthèse et appuie sur Entrée.
=SOUS.TOTAL(9; C2:C7)C2:C7 compte six cellules, mais la fonction n'en additionne que quatre : elle repère les deux lignes de sous-totaux, écrites elles aussi avec SOUS.TOTAL, et les saute pour ne pas compter deux fois les mêmes montants. La fonction SOMME, elle, avalerait tout et afficherait 152 000 € au lieu de 76 000 €.
Les erreurs fréquentes avec la fonction SOUS.TOTAL
Avec SOUS.TOTAL, les soucis viennent rarement de la syntaxe et presque toujours du code de fonction. Le piège classique : choisir un code 1-11 alors que tu attendais un 101-111, si bien que les lignes masquées à la main continuent d'être comptées et que ton total ne colle pas à l'écran. Et si tu tapes un code qui n'existe pas (15, 200…), Excel renvoie #VALEUR!.
Confusion entre codes 1-11 et 101-111 : lignes masquées incluses
Les codes 1-11 ignorent les lignes filtrées mais comptent quand même les lignes masquées manuellement. Résultat : des totaux qui ne correspondent pas à ce qui est visible à l'écran.
Solution : Prends l'habitude d'utiliser systématiquement les codes 101-111. Ils ignorent toutes les lignes masquées et évitent toute ambiguïté. Utilise 109 pour SOMME, 101 pour MOYENNE, 102 pour NB, 104 pour MAX, 105 pour MIN.
Erreur #VALEUR! avec un code de fonction inexistant
Si tu utilises un code qui n'existe pas (comme 15 ou 200), Excel retourne #VALEUR!. Les seuls codes valides sont 1-11 et 101-111.
Solution : Mémorise les codes les plus courants : 109 = SOMME, 101 = MOYENNE, 102 = NB, 104 = MAX, 105 = MIN. Si tu as un doute, consulte l'aide Excel ou utilise la saisie semi-automatique qui propose les codes disponibles.
Utiliser SOUS.TOTAL sur un tableau sans filtre : formule inutilement complexe
Certains utilisent SOUS.TOTAL partout "au cas où", même sur des tableaux qui ne seront jamais filtrés. Résultat : des formules plus complexes à lire sans aucun bénéfice.
Solution : Réserve SOUS.TOTAL aux tableaux où tu appliqueras des filtres ou masqueras des lignes. Pour les tableaux statiques, SOMME, MOYENNE, NB sont plus simples, plus lisibles et tout aussi fiables.
SOUS.TOTAL vs SOMME vs AGREGAT vs NB
Prends SOUS.TOTAL dès qu'un tableau est filtré et que tu veux le total des lignes visibles, surtout s'il contient déjà des sous-totaux qu'il faut éviter de compter deux fois. Si rien n'est jamais filtré, SOMME et NB restent plus lisibles. Et le jour où tes données contiennent des #N/A ou des #VALEUR! à ignorer, c'est AGREGAT qu'il te faut : c'est la seule des quatre à gérer les erreurs.
| Critère | SOUS.TOTAL | SOMME | AGREGAT | NB |
|---|---|---|---|---|
| Respecte les filtres | ✅ Oui | ❌ Non | ✅ Oui | ❌ Non |
| Ignore autres sous-totaux | ✅ Oui | ❌ Non | ❌ Non | ❌ Non |
| Gère les erreurs | ❌ Non | ❌ Non | ✅ Oui | ❌ Non |
| Nombre de fonctions | 11 fonctions | 1 seule | 19 fonctions | 1 seule |
| Facilité d'utilisation | ⭐⭐⭐ | ⭐⭐⭐ | ⭐⭐ | ⭐⭐⭐ |
| Cas d'usage principal | Tableaux filtrés | Additions simples | Calculs avancés | Comptages simples |
Questions fréquentes
SOUS.TOTAL est intelligent : il ignore automatiquement les lignes masquées par un filtre. Si tu filtres ton tableau de ventes par région, SOUS.TOTAL recalcule le total uniquement pour les lignes visibles. SOMME, lui, additionne toujours tout, même ce qui est caché.
Les codes 1-11 incluent les lignes masquées manuellement (clic droit > Masquer) mais ignorent les filtres automatiques. Les codes 101-111 ignorent toutes les lignes masquées, quelle que soit la méthode. Utilise 101-111 pour être sûr de ne compter que ce qui est visible à l'écran.
Non, SOUS.TOTAL propose 11 fonctions de base : MOYENNE (1/101), NB (2/102), NBVAL (3/103), MAX (4/104), MIN (5/105), PRODUIT (6/106), ECARTYPE (7/107), ECARTYPE.PEARSON (8/108), SOMME (9/109), VAR.P (10/110) et VAR (11/111). Pour les autres besoins, utilise AGREGAT (19 fonctions) ou les fonctions classiques.
C'est une protection contre le double comptage. Si tu as des sous-totaux par catégorie et un total général, SOUS.TOTAL ignore automatiquement les sous-totaux intermédiaires. Résultat : ton total général est juste, sans avoir à bricoler tes formules.
Pas vraiment, car les tableaux croisés dynamiques gèrent déjà automatiquement leurs sous-totaux et filtres. SOUS.TOTAL est fait pour les tableaux classiques où tu appliques des filtres manuellement. Pour les TCD, laisse Excel gérer les calculs.
SOUS.TOTAL propose 11 fonctions et ignore les filtres. AGREGAT propose 19 fonctions, ignore aussi les filtres, et ajoute la gestion des erreurs (il peut ignorer les cellules contenant #VALEUR! ou #N/A). Utilise AGREGAT uniquement si tu as besoin de cette gestion d'erreurs avancée, sinon SOUS.TOTAL est plus simple.
Pour aller plus loin
Continue sur ta lancée après la fonction SOUS.TOTAL : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
