Formule générique
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Sommaire
Pour additionner si les cellules contiennent une chaîne de texte ou une autre (c'est-à-dire qu'elles contiennent "chat" ou "rat"), vous pouvez utiliser la fonction SUMPRODUCT avec ISNUMBER + SEARCH ou FIND. Dans l'exemple illustré, la formule de la cellule F5 est:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
qui renvoie la somme des valeurs de C4: C8 lorsque les cellules de B4: B8 contiennent soit "chat", soit "rat".
Explication
Lorsque vous additionnez des cellules avec des critères «OU», vous devez faire attention à ne pas compter deux fois lorsqu'il y a une possibilité que les deux critères retournent vrai. Dans l'exemple illustré, nous voulons additionner les valeurs de la colonne C lorsque les cellules de la colonne B contiennent soit "chat", soit "rat". Nous ne pouvons pas utiliser les SUMIF avec deux critères, car SUMIFS est basé sur la logique AND. Et si nous essayons d'utiliser deux SUMIFS (c'est-à-dire SUMIFS + SUMIFS), nous allons compter deux fois car il y a des cellules qui contiennent à la fois "chat" et "rat".
Au lieu de cela, nous utilisons une formule comme celle-ci:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Ce noyau de cette formule est basé sur la formule expliquée ici qui localise le texte à l'intérieur d'une cellule avec ISNUMBER et SEARCH:
ISNUMBER(SEARCH("text",range)
Lorsqu'il est donné une plage de cellules, cet extrait renvoie un tableau de valeurs VRAI / FAUX, une valeur pour chaque cellule de la plage. Dans cette formule, nous utilisons cet extrait deux fois, une fois pour "cat" et une fois pour "rat", nous obtiendrons donc deux tableaux. À ce stade, nous avons:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Ensuite, nous ajoutons ces tableaux ensemble car l'addition est utilisée en algèbre booléenne pour la logique OR. L'opération mathématique contraint automatiquement les valeurs TRUE et FALSE à 1s et 0s, nous nous retrouvons donc avec le tableau ci-dessous:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Chaque nombre de ce tableau est le résultat de l'ajout des valeurs TRUE et FALSE dans les deux tableaux d'origine ensemble. Dans l'exemple illustré, le tableau ressemble à ceci:
(2;0;2;1;0)
Nous devons additionner ces chiffres, mais nous ne voulons pas compter deux fois. Nous devons donc nous assurer que toute valeur supérieure à zéro n'est comptée qu'une seule fois. Pour ce faire, nous forçons toutes les valeurs à TRUE ou FALSE en vérifiant le tableau avec "> 0". Cela renvoie TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Que nous convertissons ensuite en 1/0 en utilisant un double négatif (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
et enfin:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multiplie les éléments correspondants des deux tableaux ensemble et additionne le résultat, retournant 70.
Option sensible à la casse
La fonction RECHERCHE ignore la casse. Si vous avez besoin d'une option sensible, remplacez SEARCH par la fonction FIND.
