Formule générique
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Sommaire
Pour additionner les valeurs dans les colonnes et les lignes correspondantes, vous pouvez utiliser la fonction SUMPRODUCT. Dans l'exemple illustré, la formule en J6 est:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
où les données (C5: G14), les jours (B5: B14) et les codes (C4: G4) sont des plages nommées.
Explication
La fonction SUMPRODUCT peut gérer les tableaux de manière native, sans nécessiter l'entrée de changement de commande.
Dans ce cas, nous multiplions toutes les valeurs des données de plage nommées par deux expressions qui filtrent les valeurs sans intérêt. La première expression applique un filtre basé sur des codes:
(codes=J4)
Puisque J4 contient "A002", l'expression crée un tableau de valeurs TRUE FALSE comme ceci:
(FALSE,TRUE,FALSE,FALSE,FALSE)
La deuxième expression filtre le jour:
(days=J5)
Puisque J4 contient "Wed", l'expression crée un tableau de valeurs TRUE FALSE comme ceci:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Dans Excel, les valeurs TRUE FALSE sont automatiquement forcées aux valeurs 1 et 0 par toute opération mathématique, de sorte que l'opération de multiplication contraint les tableaux ci-dessus à des uns et des zéros, et crée un tableau 2D avec les mêmes dimensions que les données d'origine. Le processus peut être visualisé comme indiqué ci-dessous:
Enfin, SUMPRODUCT renvoie la somme de tous les éléments du tableau final, 9.
Compter au lieu de somme
Si vous souhaitez compter les valeurs correspondantes au lieu de les additionner, vous pouvez raccourcir la formule à:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Notez que ce nombre comprendra les cellules vides.
Remarques
- Bien que l'exemple ne montre qu'une seule colonne correspondante, cette formule additionnera correctement plusieurs colonnes correspondantes.
- Si vous avez seulement besoin de faire correspondre des colonnes (pas des lignes), vous pouvez utiliser une formule comme celle-ci.
- Pour ne faire correspondre que les lignes, vous pouvez utiliser la fonction COUNTIFS.
