Generische Formel
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Zusammenfassung
Um zusammenzufassen, ob Zellen entweder die eine oder die andere Textzeichenfolge enthalten (dh "Katze" oder "Ratte"), können Sie die Funktion SUMPRODUCT zusammen mit ISNUMBER + SEARCH oder FIND verwenden. In dem gezeigten Beispiel lautet die Formel in Zelle F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Dies gibt die Summe der Werte in C4: C8 zurück, wenn Zellen in B4: B8 entweder "cat" oder "rat" enthalten.
Erläuterung
Wenn Sie Zellen mit "ODER" -Kriterien summieren, müssen Sie darauf achten, nicht doppelt zu zählen, wenn die Möglichkeit besteht, dass beide Kriterien true zurückgeben. In dem gezeigten Beispiel möchten wir die Werte in Spalte C summieren, wenn die Zellen in Spalte B entweder "cat" oder "rat" enthalten. Wir können SUMIFs nicht mit zwei Kriterien verwenden, da SUMIFS auf UND-Logik basiert. Und wenn wir versuchen, zwei SUMIFS (dh SUMIFS + SUMIFS) zu verwenden, werden wir doppelt zählen, da es Zellen gibt, die sowohl "Katze" als auch "Ratte" enthalten.
Stattdessen verwenden wir eine Formel wie die folgende:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Dieser Kern dieser Formel basiert auf der hier erläuterten Formel, mit der Text innerhalb einer Zelle mit ISNUMBER und SEARCH lokalisiert wird:
ISNUMBER(SEARCH("text",range)
Wenn ein Zellbereich angegeben wird, gibt dieses Snippet ein Array von TRUE / FALSE-Werten zurück, einen Wert für jede Zelle im Bereich. In dieser Formel verwenden wir dieses Snippet zweimal, einmal für "Katze" und einmal für "Ratte", sodass wir zwei Arrays erhalten. An diesem Punkt haben wir:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Als nächstes addieren wir diese Arrays, da die Addition in der Booleschen Algebra für die ODER-Logik verwendet wird. Die mathematische Operation zwingt die Werte TRUE und FALSE automatisch auf 1s und 0s, sodass wir das folgende Array erhalten:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Jede Zahl in diesem Array ist das Ergebnis der Addition der Werte TRUE und FALSE in den beiden ursprünglichen Arrays. Im gezeigten Beispiel sieht das Array folgendermaßen aus:
(2;0;2;1;0)
Wir müssen diese Zahlen addieren, aber wir wollen nicht doppelt zählen. Wir müssen also sicherstellen, dass jeder Wert größer als Null nur einmal gezählt wird. Dazu erzwingen wir alle Werte auf TRUE oder FALSE, indem wir das Array mit "> 0" überprüfen. Dies gibt TRUE / FALSE zurück:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Was wir dann mit einem doppelten Negativ (-) in 1/0 umwandeln:
=SUMPRODUCT((1;0;1;1;0),C4:C8)
und schlussendlich:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multipliziert die entsprechenden Elemente der beiden Arrays und summiert das Ergebnis, wobei 70 zurückgegeben wird.
Groß- und Kleinschreibung beachten
Die SEARCH-Funktion ignoriert Groß- und Kleinschreibung. Wenn Sie eine vertrauliche Option benötigen, ersetzen Sie SEARCH durch die Funktion FIND.
