Chapitre 17 · 17.18
Additionner uniquement les lignes filtrées, même avec une condition supplémentaire
Vous filtrez une liste de ventes sur une seule catégorie, mais le total en dessous reste le montant de toutes les lignes. C'est normal : SOMME additionne aussi les lignes que le filtre masque. Pour additionner uniquement ce que vous voyez, utilisez SOUS.TOTAL. Et si vous souhaitez ajouter une condition supplémentaire au sein des lignes filtrées, ou ignorer les valeurs d'erreur, deux autres formules pratiques sont à votre disposition.
Prenons comme exemple la liste de ventes de Bloemisterij Het Zonnetje : la date en A, le produit en B, la catégorie en C et le montant en D, de la ligne 2 à la ligne 300. Un filtre est appliqué à la ligne d'en-tête (voir Trier et filtrer).
Où placer les formules ?
Pas sous la liste : une formule placée directement sous la liste peut elle-même être masquée lors du filtrage, et se déplacer lors du tri. Mais pas non plus n'importe où à côté de la liste : un filtre masque des lignes entières, donc une formule en F3 disparaît aussi si la ligne 3 est filtrée. Placez-les donc dans la ligne d'en-tête (ligne 1, à côté des titres), ou sur une feuille de calcul distincte. Dans l'exemple, elles se trouvent en ligne 1, à partir de la colonne F.
La somme des lignes visibles
=SOUS.TOTAL(109;D2:D300)
Le premier nombre détermine le calcul. 9 correspond à la somme, 109 aussi, avec une seule différence : 109 ignore également les lignes que vous avez masquées manuellement (clic droit, Masquer). 9 ignore uniquement les lignes masquées par le filtre. Utilisez donc 109 si vous souhaitez additionner ce que vous voyez.
| Calcul | Filtrées | Également masquées manuellement |
|---|---|---|
| Somme | 9 | 109 |
| Nombre de cellules non vides | 3 | 103 |
| Moyenne | 1 | 101 |
| Maximum | 4 | 104 |
Pour compter le nombre de lignes visibles, utilisez =SOUS.TOTAL(103;B2:B300).

Visible et une condition supplémentaire
Vous avez filtré sur la catégorie Boeketten (bouquets) et souhaitez n'additionner, parmi celles-ci, que les compositions funéraires (Rouwstuk). SOMME.SI.ENS ne tient pas compte du filtre, et SOUS.TOTAL n'accepte pas de condition. Sur Internet, vous trouverez pour cela une formule combinant SOMMEPROD, SOUS.TOTAL et DECALER. Dans Euro-Office, elle renvoie la valeur d'erreur #NOMBRE!. Avec une colonne auxiliaire, cela fonctionne, et plus simplement :
- Ajoutez à côté de la liste une colonne Peut voir (visible), ici la colonne E. Sélectionnez E2:E300, tapez =SOUS.TOTAL(103;D2) et appuyez sur Ctrl+Entrée. Chaque ligne affiche 1 tant qu'elle est visible, et 0 lorsque le filtre la masque.
- Additionnez avec SOMME.SI.ENS en ajoutant la colonne auxiliaire comme condition supplémentaire : =SOMME.SI.ENS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Si vous modifiez le filtre, la colonne auxiliaire et la somme sont immédiatement recalculées.
Ignorer les valeurs d'erreur
Si la colonne contient quelque part un #N/A ou un #DIV/0!, SOMME et SOUS.TOTAL renvoient également une valeur d'erreur. AGREGAT peut ignorer les erreurs :
=AGREGAT(9;7;D2:D300)
Le 9 correspond là encore à la somme, le 7 signifie : ignorer les lignes masquées et les valeurs d'erreur.
Pièges à éviter
- Les colonnes masquées sont bien prises en compte : SOUS.TOTAL ne tient compte que des lignes masquées.
