Additionner selon plusieurs conditions avec SUMIFS

Vous savez déjà que SUMIF totalise les valeurs qui correspondent à une seule règle, par exemple toutes les ventes de la région Est. Mais les questions réelles comportent généralement plusieurs niveaux : Quelles ont été les ventes de la région Est en janvier ? Il s'agit de deux conditions à la fois.

C'est là que SUMIFS se montre particulièrement utile. Le S final signifie que la fonction peut combiner de nombreux critères, et une ligne n'est ajoutée au total que lorsqu'elle satisfait tous les critères que vous lui donnez.

Dans cette leçon, vous apprendrez l'ordre des arguments, vous écrirez votre première somme à plusieurs critères et vous éviterez les erreurs classiques qui posent problème.

SUMIFS inverse l'ordre auquel vous pourriez vous attendre avec SUMIF. Les nombres à additionner viennent en premier, puis chaque condition est fournie sous forme de paire.

• sum_range — les valeurs à totaliser
• criteria_range1, criteria1 — le premier critère
• criteria_range2, criteria2 — le deuxième critère

Vous pouvez ajouter des paires de plages et de critères jusqu'à 127 conditions. Lisez le schéma ci-dessous à voix haute : additionnez ceci, lorsque ceci est égal à cela, et lorsque ceci est égal à cela.

Imaginez un tableau dans lequel la colonne A contient la Région, la colonne B le Mois et la colonne C le Montant. Vous voulez obtenir le total des ventes de la région Est en janvier.

Les montants à additionner se trouvent dans C:C. La première condition vérifie A:A pour « Est », et la deuxième vérifie B:B pour « Janvier ». Une ligne n'est comptée que lorsque les deux conditions sont vraies.

Il s'agit de l'erreur la plus courante avec SUMIFS. La plage de somme et chaque plage de critères doivent avoir des dimensions identiques — le même nombre de lignes et de colonnes.

Si votre plage de somme est C2:C100 mais qu'une plage de critères est A2:A99, la formule renvoie une erreur #VALUE!, car les lignes ne correspondent pas.

Le plus sûr consiste à utiliser les mêmes lignes de début et de fin pour toutes les plages, ou à utiliser systématiquement des colonnes entières comme A:A.

Inscrire « Est » entre guillemets fonctionne, mais un rapport modulable permet à l'utilisateur de faire son choix. Saisissez la région dans la cellule F1 et le mois dans F2, puis utilisez ces cellules comme critères.

La modification de F1 ou de F2 recalcule alors instantanément le total. Notez qu'une simple référence de cellule ne nécessite aucun guillemet — les guillemets servent uniquement au texte littéral saisi dans la formule.

Les critères ne se limitent pas au texte exact. Vous pouvez utiliser des opérateurs de comparaison pour les nombres en les plaçant entre guillemets.

• ">100" — supérieur à 100
• "<=50" — inférieur ou égal à 50
• "<>0" — différent de zéro

Nous totalisons ici les montants de la région Est, mais uniquement les lignes dont le montant lui-même est supérieur à 100. Notez que la plage de somme et une plage de critères peuvent désigner la même colonne.

Que faire si le seuil se trouve dans une cellule au lieu d'être saisi directement ? Vous ne pouvez pas simplement écrire ">F1" — cela recherche le texte littéral F1. Vous devez plutôt associer l'opérateur à la cellule avec le symbole &.

Ainsi, ">"&F1 construit le critère supérieur à tout ce que contient F1. Cette astuce de concaténation est essentielle pour les rapports dynamiques pilotés par l'utilisateur.

SUMIFS s'adapte facilement. Ajoutez une paire de plage et de critère pour chaque nouvelle règle. Supposons que la colonne D contienne le Commercial. Vous pouvez totaliser les ventes de la région Est, en janvier, effectuées par « Maria ».

Chaque condition restreint davantage le résultat. Comme SUMIFS utilise une logique AND, une ligne doit satisfaire les trois critères pour être incluse dans la somme.

Les critères textuels acceptent les caractères génériques. L'astérisque * correspond à un nombre quelconque de caractères et le point d'interrogation ? correspond exactement à un caractère.

• "North*" correspond à Nord, Nord-est et Nord-ouest
• "*east*" correspond à tout ce qui contient est

Cela totalise les montants de toute région qui commence par « Nord », ce qui est pratique lorsque les noms de régions partagent le même préfixe.

Il vaut la peine de mémoriser la différence, car l'ordre des arguments est inversé :

• SUMIF : range, criteria, [sum_range] — la plage à vérifier vient en premier ; la plage de somme est facultative et vient en dernier.
• SUMIFS : sum_range, criteria_range1, criteria1, ... — la plage de somme vient toujours en premier.

Conseil : dès que vous avez plus d'une condition, utilisez directement SUMIFS. Beaucoup de personnes utilisent SUMIFS même pour un seul critère afin de conserver un schéma unique.

Lorsque SUMIFS renvoie 0, cela signifie généralement qu'aucune ligne ne correspond à toutes les conditions — et non que la formule est incorrecte. Vérifiez l'absence d'espaces invisibles dans votre texte, l'orthographe et le fait qu'un nombre ne soit pas enregistré comme du texte.

Pour effectuer un diagnostic rapide, supprimez une condition à la fois. Si le total apparaît dès que vous retirez un critère, c'est ce critère qui excluait toutes les lignes. Cette habitude consistant à isoler puis à vérifier rend le débogage des formules à plusieurs critères rapide.

Vérifiez votre compréhension de l'ordre des arguments de SUMIFS.

Vous pouvez maintenant totaliser des nombres soumis à plusieurs conditions à la fois :

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — la plage de somme vient en premier.
• Toutes les plages doivent avoir la même taille, sinon vous obtenez #VALUE!.
• Utilisez ">"&F1 pour comparer à une cellule, et * / ? pour les caractères génériques.
• Les conditions utilisent une logique AND — une ligne doit les satisfaire toutes.

Ensuite : compter les lignes selon plusieurs conditions avec COUNTIFS.