Formula generica
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Sommario
Per sommare se le celle contengono una stringa di testo o un'altra (cioè contengono "gatto" o "ratto") è possibile utilizzare la funzione SUMPRODUCT insieme a ISNUMBER + SEARCH o FIND. Nell'esempio mostrato, la formula nella cella F5 è:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
che restituisce la somma dei valori in C4: C8 quando le celle in B4: B8 contengono "gatto" o "ratto".
Spiegazione
Quando si sommano celle con criteri "OR", è necessario fare attenzione a non conteggiare due volte quando è possibile che entrambi i criteri restituiscano true. Nell'esempio mostrato, vogliamo sommare i valori nella colonna C quando le celle nella colonna B contengono "gatto" o "ratto". Non possiamo utilizzare SUMIF con due criteri, perché SUMIFS si basa sulla logica AND. E se proviamo a usare due SUMIFS (cioè SUMIFS + SUMIFS) conteggeremo due volte perché ci sono celle che contengono sia "cat" che "rat".
Invece, usiamo una formula come questa:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Questo nucleo di questa formula si basa sulla formula spiegata qui che individua il testo all'interno di una cella con ISNUMBER e SEARCH:
ISNUMBER(SEARCH("text",range)
Quando viene fornito un intervallo di celle, questo frammento restituirà una matrice di valori VERO / FALSO, un valore per ogni cella dell'intervallo. In questa formula, usiamo questo snippet due volte, una per "cat" e una per "rat", quindi otterremo due array. A questo punto abbiamo:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Successivamente, aggiungiamo questi array insieme perché l'addizione viene utilizzata nell'algebra booleana per la logica OR. L'operazione matematica forza automaticamente i valori VERO e FALSO su 1 e 0, quindi finiamo con l'array seguente:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Ogni numero in questo array è il risultato dell'aggiunta dei valori TRUE e FALSE nei due array originali insieme. Nell'esempio mostrato, l'array ha questo aspetto:
(2;0;2;1;0)
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 fare ciò, forziamo tutti i valori a TRUE o FALSE controllando l'array con "> 0". Ciò restituisce VERO / FALSO:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Che poi convertiamo in 1/0 usando un doppio negativo (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
e infine:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT moltiplica insieme gli elementi corrispondenti dei due array e somma il risultato, restituendo 70.
Opzione case-sensitive
La funzione RICERCA ignora le maiuscole. Se hai bisogno di un'opzione sensibile, sostituisci CERCA con la funzione TROVA.
