Comment utiliser COUNTIFS, SUMIFS, AVERAGEIFS dans Excel

Les trois formules les plus couramment utilisées dans Excel pour effectuer des calculs mathématiques simples sont COUNT , SUM et AVERAGE . Que vous gériez un budget financier dans Excel ou que vous suiviez simplement vos prochaines vacances, vous avez probablement déjà utilisé l'une de ces fonctions auparavant.

Dans cet article, nous allons passer en revue les bases de ces trois fonctions et leurs équivalents pertinents et utiles : COUNTIFS, SUMIFS et AVERAGEIFS.

Disons que nous commençons une nouvelle activité en ligne vendant des téléphones portables et que nous avons une feuille qui répertorie les ventes que nous avons réalisées au cours des deux premiers mois. Téléchargez un exemple de feuille de calcul Excel ici .

Excel COMPTE, SOMME et MOYENNE

Pour savoir combien de téléphones portables nous avons vendus, nous pouvons rapidement utiliser la formule COUNT comme indiqué ci-dessous :

=COMPTE(E2:E16)

D'autre part, pour obtenir le montant total des ventes que nous avons réalisées, nous pouvons utiliser la formule SOMME comme indiqué ci-dessous :

=SOMME(E2:E16)

Enfin, pour connaître les ventes moyennes que nous avons réalisées pour tous les téléphones, nous pouvons utiliser la formule MOYENNE comme ci-dessous :

=MOYENNE(E2:E16)

Le résultat devrait être comme ci-dessous :

Les formules COUNT, SUM et AVERAGE ne fonctionneront que pour les enregistrements où la valeur de la cellule est au format numérique. Tout enregistrement dans la plage de formules (c'est-à-dire E2:E16 dans cet exemple) qui n'est pas au format numérique sera ignoré.

Assurez-vous donc que toutes les cellules des formules COUNT, SUM et AVERAGE sont toutes formatées en tant que Number , et non Text . Essayez d'utiliser la même formule, mais avec E:E comme plage au lieu de E2:E16 . Il renverra le même résultat qu'auparavant car il ignore l'en-tête (c'est-à-dire le prix de vente ), qui est au format texte.

Maintenant, que se passe-t-il si nous voulons connaître le nombre de ventes, le montant total des ventes et le montant moyen des ventes par téléphone, uniquement pour ceux vendus aux États-Unis ? C'est là que COUNTIFS, SUMIFS et AVERAGEIFS jouent un rôle important. Observez la formule ci-dessous :

NB.SI

Répartition de la formule :

1. =COUNTIFS( - Le "=" indique le début d'une formule dans la cellule et COUNTIFS est la première partie de la fonction Excel que nous utilisons.
2. D2:D16 - Fait référence à la plage de données à vérifier pour voir si elle répond aux critères à inclure dans la formule de comptage.
3. "USA" - Critères à rechercher dans la plage de données spécifiée ( D2: D16 )
4. ) – Parenthèse fermante indiquant la fin de la formule.

La formule renvoie 6 qui est le nombre de ventes pour les produits expédiés depuis l'entrepôt américain.

SOMMES

Répartition de la formule :

1. =SUMIFS( – Le "=" indique à nouveau le début de la formule.
2. E2:E16 - Fait référence à la plage de données que nous aimerions totaliser, c'est-à-dire le prix de vente dans notre exemple.
3. D2:D16 - Fait référence à la plage de données à vérifier pour voir si elle répond aux critères à inclure dans le montant total.
4. "USA" - Critères à rechercher dans la plage de données spécifiée ( D2: D16 )
5. ) – Parenthèse fermante indiquant la fin de la formule.

La formule indique des ventes totales de 6 050 $ réalisées pour les produits expédiés depuis l'entrepôt américain.

MOYENNE SI

Répartition de la formule :

1. =AVERAGEIFS( – Les "=" indiquent le début de la formule.
2. E2:E16 - Fait référence à la plage de données dont nous aimerions faire la moyenne. Dans cet exemple, nous voulons obtenir le montant moyen des ventes pour tous les téléphones vendus aux États-Unis.
3. D2:D16 – Fait référence à la plage de données à vérifier pour voir si elle satisfait aux critères à inclure dans la formule moyenne.
4. "USA" - Critères à rechercher dans la plage de données spécifiée
5. ) – Parenthèse fermante indiquant les extrémités de la formule.

La formule montre que nous avons vendu le produit pour environ 1 008 $ par téléphone aux États-Unis.

Les trois formules peuvent prendre plus d'un critère. Par exemple, si nous voulons connaître les mêmes chiffres (c'est-à-dire COUNT , SUM et AVERAGE ) pour les produits vendus aux États-Unis , mais spécifiquement uniquement pour la  marque Samsung , il suffit d'ajouter la plage de données à vérifier suivie de ses critères.

Veuillez voir l'exemple ci-dessous où un deuxième critère est ajouté aux vérifications initiales des critères. (Le texte bleu indique le premier critère et le rouge indique le deuxième critère)

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung") =SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung") =AVERAGEIFS(E2:E16,D2 :D16,"États-Unis", B2:B16,"Samsung")

Vous remarquerez qu'Excel possède également  les formules COUNTIF , SUMIF et AVERAGEIF sans le suffixe "S" . Ceux-ci sont utilisés de la même manière que COUNTIFS , SUMIFS et AVERAGEIFS . Cependant, ceux sans le suffixe "S" dans la formule ont la limitation de n'autoriser qu'un seul critère par formule.

Comme la syntaxe est légèrement différente, je recommanderais d'utiliser  COUNTIFS , SUMIFS et AVERAGEIFS uniquement car il peut être utilisé pour un ou plusieurs critères, si nécessaire. Prendre plaisir!