Utilisez COUNTIF | COUNTIFS pour exclure les lignes masquées dans Google Sheets

Dans ce tutoriel Google Sheets, je vais vous expliquer comment utiliser COUNTIF ou COUNTIFS pour exclure les lignes masquées ou filtrées.

COUNTIF ne peut gérer qu’un seul critère, donc nous ne pouvons pas l’utiliser pour exclure les lignes masquées dans Google Sheets. Nous devons spécifier au moins deux conditions pour le comptage conditionnel : une condition pour la valeur souhaitée et une condition pour exclure les lignes masquées.

Par conséquent, nous devons utiliser COUNTIFS, même si nous ne voulons appliquer qu’un seul critère. Pour exclure les lignes masquées dans un comptage conditionnel, nous utiliserons COUNTIFS, et non COUNTIF.

Commencer avec COUNTIFS en excluant les lignes masquées dans Google Sheets

Savez-vous combien de façons il y a de masquer une ligne ou plusieurs lignes dans Google Sheets ?

À ma connaissance, dans Google Sheets, il existe quatre façons intégrées de masquer les lignes :

1. Sélectionnez les lignes que vous souhaitez masquer, faites un clic droit, puis sélectionnez « Masquer les lignes 6 – 7 » (ou les numéros de ligne correspondants).
2. Cliquez sur la liste déroulante du filtre dans la cellule C1 (nous avons déjà filtré nos données d’échantillon), décochez « Victor » et « Ralph », puis cliquez sur le bouton OK. (C’est la méthode que nous suivons.)
3. Sélectionnez les lignes que vous souhaitez masquer, faites un clic droit, puis sélectionnez « Afficher plus d’actions sur les lignes » et « Regrouper les lignes 6 – 7 » (ou les numéros de ligne correspondants). Ensuite, basculez le bouton + ou -.
4. Utilisez le Slicer (Menu Données > Ajouter un slicer).

Une fois que vous avez masqué les lignes en utilisant l’une des méthodes ci-dessus, insérez la formule suivante dans la cellule C13 :

=SOUSTOTAL(103,C2:C11)

Cela renverra 8.

Le décompte réel est de 10, mais le décompte en excluant les lignes masquées est de 8. C’est parce que SOUSTOTAL considère le décompte des valeurs dans les lignes masquées comme 0.

Nous utiliserons la même méthode avec SOUSTOTAL dans COUNTIFS pour exclure les lignes masquées dans Google Sheets, car COUNTIF ou COUNTIFS n’a pas de fonctionnalité pour exclure les lignes masquées.

Comment utiliser COUNTIFS en excluant les lignes masquées dans Google Sheets

Pour notre objectif, nous pouvons utiliser SOUSTOTAL de deux manières :

1. Dans une plage de colonnes auxiliaires.
2. Dans une formule matricielle utilisant MAP Lambda.

Supposons que vous souhaitiez obtenir le décompte des valeurs dans D2:D où les valeurs sont supérieures ou égales à 3000. Voici la formule COUNTIF habituelle :

=COUNTIF(D2:D11, ">=3000")

Voici l’alternative COUNTIFS :

=COUNTIFS(D2:D11, ">=3000")

Convertissons cette formule COUNTIFS pour exclure les lignes masquées :

Syntaxe de la fonction COUNTIFS :

COUNTIFS(plage_critère1, critère1, [plage_critère2, ...], [critère2, ...])

Nous avons utilisé plage_critère1 dans la formule qui est D2:D11. Nous devrions effectuer un test SOUSTOTAL sur cette colonne. Si vous avez deux colonnes (plage_critère1 et plage_critère2), vous pouvez choisir l’une des colonnes pour le test.

Le test SOUSTOTAL peut être une formule de tableau ou non. Voyons cela ci-dessous dans des exemples de COUNTIFS excluant les lignes cachées ou filtrées.

1. Approche de la colonne auxiliaire

Suivez ces étapes :

1. Affichez les lignes masquées, le cas échéant (nous avons masqué deux lignes dans l’exemple précédent, n’est-ce pas ?).
2. Dans la cellule E2, insérez la formule SOUSTOTAL suivante et copiez-la et collez-la vers le bas jusqu’à la cellule E11, ou faites glisser la poignée de remplissage E2 vers le bas jusqu’à la cellule E11 :

=SOUSTOTAL(103,D2)

Voici la syntaxe de la fonction SOUSTOTAL :

SOUSTOTAL(code_fonction, plage1, [plage2, ...])

Où :

• 103 est le code de fonction de la fonction DÉCOMPTE.
• plage1 est D2, la première cellule à tester dans D2:D11, qui est la colonne utilisée dans la formule COUNTIFS ci-dessus.

Dans la cellule F2, insérez la formule COUNTIFS suivante :

=COUNTIFS(D2:D11, ">=3000", E2:E11, "=1")

Où :

• D2:D11 est la plage_critère1.
• ">=3000" est le critère1.
• E2:E11 est la plage_critère2.
• "=1" est le critère2.

Ce COUNTIFS exclura les lignes masquées dans le résultat.

Comment est-ce possible ?

La fonction SOUSTOTAL renvoie 0 dans les lignes masquées et 1 dans les lignes visibles. Nous avons utilisé cela comme critère dans COUNTIFS pour exclure les valeurs de lignes masquées dans le résultat.

2. COUNTIFS excluant les lignes masquées sans colonne auxiliaire

SOUSTOTAL est l’une des fonctions Google Sheets qui ne s’étend pas d’elle-même ou avec l’aide de la fonction ARRAYFORMULA. C’est là que la fonction LAMBDA entre en jeu.

Si vous ne voulez pas faire glisser la fonction SOUSTOTAL dans la cellule E2 vers le bas, vous pouvez utiliser la formule MAP suivante dans la cellule E2 :

=MAP(D2:D11, LAMBDA(row, SOUSTOTAL(103, row)))

Note : Vous devez supprimer toutes les valeurs dans E2:E11, même dans les lignes masquées, avant d’entrer cette formule dans la cellule E2. Si les formules non-array SOUSTOTAL précédentes se trouvent dans l’une des cellules de cette plage, cela renverra une erreur #REF!.

Voici la syntaxe de la fonction d’aide MAP lambda :

MAP(array1, lambda(nom, expression_formule))

Où :

• D2:D11 est array1.
• row est le nom de array1.
• SOUSTOTAL(103, row) est expression_formule.

La fonction MAP itère sur chaque valeur de array1 pour renvoyer un tableau de résultats SOUSTOTAL.

Pouvons-nous utiliser cette formule dans COUNTIFS au lieu de l’utiliser dans la cellule E2 ?

Oui ! Remplacez simplement E2:E11 dans la formule COUNTIFS basée sur la colonne auxiliaire par la formule MAP ci-dessus, comme ceci :

=COUNTIFS(D2:D11, ">=3000", MAP(C2:C11, LAMBDA(row, SOUSTOTAL(103, row))), "=1")

Vous pouvez utiliser la formule ci-dessus pour COUNTIFS en excluant les lignes masquées dans Google Sheets.

Ressources

Voici quelques ressources connexes :

1. Google Sheets Query Hidden Row Handling with Virtual Helper Column.
2. SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column].
3. How to Omit Hidden or Filtered out Values in Sum [Google Doc Spreadsheet].
4. Vlookup Skips Hidden Rows in Google Sheets – Formula Example.
5. Check Whether a Row Is Hidden and Highlight the Row above It in Google Sheets.
6. Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets.
7. Subtotal with Condition in Google Sheets [Step by Step Guide].
8. Grouping and Subtotal in Google Sheets and Excel.
9. How to Subtotal Up to the First Blank Cell in a Column in Google Sheets.
10. Subtotal Function With Conditions in Excel and Google Sheets.