Formula generica
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Sommario
Per contare quante volte una parola specifica (o qualsiasi sottostringa) appare all'interno di un intervallo di celle, puoi utilizzare una formula basata sulle funzioni SOSTITUISCI, LUNGHEZZA e SOMMA. Nell'esempio mostrato, la formula in C11 è:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Nota: la formula in questa pagina conta le istanze di una parola in un intervallo. Ad esempio, se una cella contiene due istanze di una parola, contribuirà con 2 al conteggio totale. Se vuoi solo contare le celle che contengono una parola specifica, vedi questa semplice formula basata sulla funzione CONTA.SE.
Spiegazione
Nella versione generica della formula, rng rappresenta l'intervallo da controllare e txt è la parola o la sottostringa da contare.
Nell'esempio mostrato, B5: B8 è l'intervallo da controllare e C2 contiene il testo (parola o sottostringa) da contare.
Per ogni cella nell'intervallo, SOSTITUISCI rimuove la sottostringa dal testo originale e LUNGH calcola la lunghezza del testo senza la sottostringa. Questo numero viene quindi sottratto dalla lunghezza del testo originale. Il risultato è il numero di caratteri che sono stati rimossi da SUBSTITUTE.
Quindi, il numero di caratteri rimossi viene diviso per la lunghezza della sottostringa. Quindi, se una sottostringa o una parola è lunga 5 caratteri e ci sono 10 caratteri mancanti dopo che è stata rimossa dal testo originale, sappiamo che la sottostringa / parola è apparsa due volte nel testo originale.
Poiché il calcolo precedente è inserito nella funzione SUMPRODUCT, il risultato è una matrice che contiene un numero per ogni cella dell'intervallo. Questi numeri rappresentano il numero di occorrenze della sottostringa in ogni cella. Per questo esempio, l'array ha il seguente aspetto: (1; 1; 0; 1)
Infine, SUMPRODUCT somma tutti gli elementi nell'array per ottenere le occorrenze totali della sottostringa nell'intervallo di celle.
Ignorando il caso
SOSTITUISCI è una funzione che fa distinzione tra maiuscole e minuscole, quindi corrisponderà al maiuscolo / minuscolo quando si esegue una sostituzione. Se devi contare le occorrenze sia maiuscole che minuscole di una parola o sottostringa, usa la funzione MAIUSC all'interno di SOSTITUISCI per convertire il testo in maiuscolo prima di eseguire la sostituzione:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
