Formuła ogólna
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Podsumowanie
Aby zliczyć komórki zawierające x lub y, możesz użyć formuły opartej na funkcji SUMA PRODUKTU. W przedstawionym przykładzie formuła w komórce F5 to:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
To jest rozwiązanie formuły jednokomórkowej, wyjaśnione poniżej. Możliwe jest również użycie prostszej formuły opartej na kolumnie pomocniczej, również wyjaśnionej poniżej.
Wyjaśnienie
Kiedy liczysz komórki za pomocą logiki „LUB”, musisz uważać, aby nie policzyć podwójnie. Na przykład, jeśli zliczasz komórki, które zawierają „abc” lub „def”, nie możesz po prostu dodać dwóch funkcji LICZ.JEŻELI, ponieważ możesz podwójnie policzyć komórki zawierające zarówno „abc”, jak i „def”.
Rozwiązanie jednokomórkowe
Dla pojedynczej formuły możesz użyć SUMPRODUCT z ISNUMBER + FIND. Wzór w F5 to:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Ta formuła jest oparta na formule wyjaśnionej tutaj, która lokalizuje tekst w komórce:
ISNUMBER(FIND("abc",B5:B11)
Po podaniu zakresu komórek ten fragment kodu zwróci tablicę wartości PRAWDA / FAŁSZ, po jednej wartości dla każdej komórki zakresu. Ponieważ używamy tego dwukrotnie (raz dla „abc” i raz dla „def”), otrzymamy dwie tablice.
Następnie dodajemy te tablice razem (za pomocą +), co tworzy nową pojedynczą tablicę liczb. Każda liczba w tej tablicy jest wynikiem dodania razem wartości PRAWDA i FAŁSZ w dwóch oryginalnych tablicach. W pokazanym przykładzie wynikowa tablica wygląda następująco:
(2;0;2;0;1;0;2)
Musimy dodać te liczby, ale nie chcemy liczyć podwójnie. Musimy więc upewnić się, że każda wartość większa niż zero jest policzona tylko raz. Aby to zrobić, wymuszamy wszystkie wartości na PRAWDA lub FAŁSZ za pomocą "> 0", a następnie na 1/0 za pomocą podwójnie ujemnego (-).
Na koniec SUMPRODUCT zwraca sumę wszystkich wartości w tablicy.
Rozwiązanie kolumny pomocniczej
Dzięki kolumnie pomocniczej, która sprawdza każdą komórkę indywidualnie, problem jest mniej złożony. Możemy użyć LICZ.JEŻELI z dwiema wartościami (podanymi jako „stała tablicowa”). Wzór w C5 to:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
LICZ.JEŻELI zwróci tablicę zawierającą dwa elementy: licznik dla „abc” i licznik dla „def”. Aby zapobiec podwójnemu liczeniu, dodajemy elementy w górę, a następnie wymuszamy na wyniku PRAWDA / FAŁSZ z „> 0”. Na koniec konwertujemy wartości PRAWDA / FAŁSZ na 1 i 0 za pomocą podwójnego ujemnego (-).
Ostateczny wynik to 1 lub 0 dla każdej komórki. Aby uzyskać sumę wszystkich komórek w zakresie, po prostu zsumuj kolumnę pomocniczą.
