Formula generica
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Sommario
Per contare le celle che contengono x o y, puoi utilizzare una formula basata sulla funzione SUMPRODUCT. Nell'esempio mostrato, la formula nella cella F5 è:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Questa è la soluzione con formula a cella singola, spiegata di seguito. È anche possibile utilizzare una formula più semplice basata su una colonna helper, spiegata anche di seguito.
Spiegazione
Quando si contano le celle con "logica OR", è necessario fare attenzione a non conteggiare due volte. Ad esempio, se stai contando celle che contengono "abc" o "def", non puoi semplicemente sommare due funzioni CONTA.SE, perché puoi conteggiare due volte le celle che contengono sia "abc" che "def".
Soluzione monocellulare
Per una singola formula, puoi utilizzare SUMPRODUCT con ISNUMBER + FIND. La formula in F5 è:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Questa formula si basa sulla formula spiegata qui che individua il testo all'interno di una cella:
ISNUMBER(FIND("abc",B5:B11)
Quando viene fornito un intervallo di celle, questo frammento restituirà una matrice di valori VERO / FALSO, un valore per ogni cella dell'intervallo. Dato che lo stiamo usando due volte (una per "abc" e una per "def"), otterremo due array.
Successivamente, aggiungiamo questi array insieme (con +), che crea un nuovo singolo array di numeri. Ogni numero in questo array è il risultato dell'aggiunta dei valori TRUE e FALSE nei due array originali insieme. Nell'esempio mostrato, l'array risultante ha questo aspetto:
(2;0;2;0;1;0;2)
Dobbiamo sommare questi numeri, ma non vogliamo contarli due volte. Quindi dobbiamo assicurarci che qualsiasi valore maggiore di zero venga conteggiato una sola volta. Per farlo, forziamo tutti i valori a VERO o FALSO con "> 0", quindi a 1/0 con il doppio negativo (-).
Infine, SUMPRODUCT restituisce la somma di tutti i valori nella matrice.
Soluzione della colonna helper
Con una colonna helper per controllare singolarmente ogni cella, il problema è meno complesso. Possiamo usare COUNTIF con due valori (forniti come una "costante di matrice"). La formula in C5 è:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
CONTA.SE restituirà un array che contiene due elementi: un conteggio per "abc" e un conteggio per "def". Per evitare il doppio conteggio, aggiungiamo gli elementi e quindi forziamo il risultato su VERO / FALSO con "> 0". Infine, convertiamo i valori VERO / FALSO in 1 e 0 con un doppio negativo (-).
Il risultato finale è 1 o 0 per ogni cella. Per ottenere un totale per tutte le celle nell'intervallo, somma semplicemente la colonna helper.
