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: COMPTER, SOMME et MOYENNE. Que vous gériez un budget financier dans Excel ou que vous surveilliez 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 contreparties pertinentes et utiles: COUNTIFS, SUMIFS et AVERAGEIFS..

Supposons que nous démarrions une nouvelle entreprise de vente en ligne de téléphones mobiles et que nous ayons une feuille reprenant les ventes réalisées au cours des deux premiers mois.. Téléchargez l'exemple de feuille de calcul Excel ici.

Excel COUNT, SUM et MOYENNE

Pour savoir combien de téléphones mobiles que nous avons vendus, nous pouvons utiliser rapidement le COMPTER formule ci-dessous:

= COUNT (E2: E16)

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

= SUM (E2: E16)

Enfin, pour connaître les ventes moyennes que nous avons réalisées pour tous les téléphones, nous pouvons utiliser le MOYENNE formule 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 dont la valeur de cellule est au format numérique. Tout enregistrement compris dans la plage de la formule (c.-à-d.. E2: E16 dans cet exemple) pas dans le format numérique sera ignoré.

Assurez-vous donc que toutes les cellules de la formule COUNT, SUM et AVERAGE sont toutes formatées comme suit: Nombre, ne pas Texte. Essayez d’utiliser la même formule, mais avec E: E comme la gamme au lieu de E2: E16. Il retournera le même résultat qu'auparavant car il ignore l'en-tête (c'est-à-dire. Prix ​​de vente), qui est au format texte.

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

PAYTIFS

Répartition de la formule:

1. = PAYTIFS ( - le "=" indique le début d'une formule dans la cellule et PAYTIFS est la première partie de la fonction Excel que nous utilisons.
2. D2: D16 - Fait référence à une plage de données pour vérifier si elles répondent aux critères à inclure dans la formule de comptage.
3. "ETATS-UNIS" - Critères à rechercher dans la plage de données spécifiée (D2: D16)
4. ) - Crochet de fermeture indiquant la fin de la formule.

La formule renvoie 6, qui correspond au nombre de ventes de produits expédiés de l'entrepôt américain..

SUMIFS

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 à une gamme de données pour vérifier si elles répondent aux critères à inclure dans le montant total.
4. "ETATS-UNIS" - Critères à rechercher dans la plage de données spécifiée (D2: D16)
5. ) - Crochet de fermeture indiquant la fin de la formule.

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

MOYENNE

Répartition de la formule:

1. = AVERAGEIFS ( - le "=" indiquer le début de la formule.
2. E2: E16 - Fait référence à la plage de données que nous aimerions moyenner. 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 à une plage de données pour vérifier si elles répondent aux critères à inclure dans la formule de moyenne.
4. "ETATS-UNIS" - Critères à rechercher dans la plage de données spécifiée
5. ) - Crochet de fermeture 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 Etats-Unis.

Les trois formules peuvent prendre plus d’un critère. Par exemple, si nous voulons connaître les mêmes chiffres (c.-à-d.. COMPTER, SOMME et MOYENNE) pour les produits vendus dans Etats-Unis, mais spécifiquement que pour le Samsung marque, 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 contrôles de critères initiaux. (Le texte bleu indique le premier critère et le rouge indique le deuxième critère)

= PAYTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Vous remarquerez qu'Excel a aussi COUNTIF, SUMIF et MOYENNEIF formules sans suffixe "S". Ceux sont utilisés similaires à PAYTIFS, SUMIFS et MOYENNE. Cependant, ceux sans suffixe "S" dans la formule ont la limitation de ne permettre qu'un critère par formule.

Comme la syntaxe est légèrement différente, je recommanderais d'utiliser PAYTIFS, SUMIFS et MOYENNE seulement comme il peut être utilisé pour un critère ou plus, si nécessaire. Prendre plaisir!