Comment utiliser SUBTOTAL avec IF dans Excel ?

CETTE FORMULE EST UNE PURE MAGIE.

Si vous souhaitez écrire une formule SUBTOTAL dans Excel avec IF (condition), vous devez utiliser plusieurs fonctions pour ce faire. Mais avant de faire cela, comprenons les données dont nous disposons pour cet exemple.

Dans l’exemple ci-dessus, vous avez trois colonnes :

Nom
Dalle d’âge
Genre

Et lorsque vous filtrez une dalle de la colonne Age Slab, elle affiche le nombre de femmes dans la cellule F1. Cela signifie donc que nous avons une formule qui affiche le nombre de valeurs filtrées mais avec une condition.

La formule que nous avons :

=SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

Comprendre la formule SUBTOTAL IF

Cette formule utilise cinq fonctions : SUMPRODUCT, SUBTOTAL, OFFSET, ROW et MAX. Par conséquent, pour comprendre cette formule, nous devons la diviser en plusieurs parties.

1. LIGNE(C2:C41)-2,0)

Cette partie de la formule utilise les fonctions MIN et ROW.

Dans le ROW, nous avons fait référence aux colonnes « Sexe », et il renvoie un tableau de numéros de ligne.
Après cela, MIN prend ce tableau ou ces numéros de ligne et renvoie le numéro de ligne minimum. C’est pourquoi nous avons 2 dans cette partie de la formule.

2. LIGNE(C2:C41)

Dans cette partie, nous n’avons que la fonction ROW, qui renvoie un tableau de numéros de lignes.

fonction-ligne-pour-retourner-tableau-de-numéros-lignes

3. DÉCALAGE(C2,LIGNE(C2:C41)-MIN(LIGNE(C2:C41)),0)

Nous avons maintenant la fonction OFFSET. Il vous aide à créer une référence à une plage en utilisant une référence de cellule comme point de départ. Dans l’argument de référence , nous avons fait référence à la cellule C2, la première cellule à partir de laquelle commence notre plage de genre.

Dans l’argument des lignes, nous avons cette partie de la formule discutée ci-dessus dans les deux premières parties. Après cela, dans l’argument cols, nous avons utilisé 0. Avec tout cela, OFFSET renvoie un tableau de toutes les valeurs de la colonne « Sexe ».

décalage-renvoie-la-valeur-des-colonnes-de-sexe

4. SOUS-TOTAL(3,DECALAGE(C2,LIGNE(C2:C41)-MIN(LIGNE(C2:C41)),0))

Nous avons utilisé le tableau renvoyé par le DECALAGE dans le SOUS-TOTAL. Et dans le function_num, nous avons utilisé 3, ce qui indique à SUBTOTAL d’utiliser la fonction COUNTA pour le calcul.

Lorsque vous utilisez un filtre sur la colonne « Age Slab », cette partie SOUS-TOTAL de la formule renvoie un tableau contenant 0 et 1.

Dans ce tableau, nous avons 1 pour les valeurs équivalentes à la valeur pour laquelle nous avons appliqué le filtre. Voir l’exemple ci-dessous :

valeurs-équivalentes-aux-valeurs-appliquées

5. (C2:C41=E1)

Cette partie de la formule renvoie un tableau en testant une condition. Dans cette condition, nous testons si la valeur de la plage est « Female », et elle renvoie TRUE et FALSE dans le tableau.

Dans ce tableau, nous avons TRUE pour la valeur « Female » et FALSE pour les autres.

7. Dernière partie

Au final, nous avons deux tableaux dans SUMPRODUCT. Et nous avons également un opérateur astérisque entre ces tableaux.

Lorsque nous multiplions les deux tableaux l’un avec l’autre, nous avons un seul tableau avec 0 et 1. Dans ce tableau, un (1) est pour la valeur « Femme » dans le sexe et 21-30 pour « Age Slab ».

Au final, SOMMEPROD renvoie la somme en utilisant ce tableau. Et cette somme est le nombre de cellules avec la valeur « Femme » dans la colonne sexe lorsque vous filtrez la tranche 21-30 dans la colonne « Tranche d’âge ».