Come utilizzare più condizioni in Excel COUNTIF e COUNTIFS

Excel ha molte funzioni in cui l'utente deve specificare una o più condizioni per ottenere il risultato.Ad esempio, se desideri contare le celle in base a più criteri, puoi utilizzare le funzioni CONTASE o CONTASE in Excel.

Questo tutorial illustra vari modi per utilizzare criteri singoli o multipli nelle funzioni COUNTIF e COUNTIFS di Excel.

Sebbene in questo tutorial mi concentrerò principalmente sulle funzioni COUNTIF e COUNTIFS, tutti questi esempi possono essere utilizzati anche con altre funzioni di Excel che accettano più criteri come input (come SUMIF, SUMIFS, AVERAGEIF e AVERAGEIFS).

Introduzione alle funzioni COUNTIF e COUNTIFS di Excel

Capiamo prima come utilizzare le funzioni COUNTIF e COUNTIFS in Excel.

Funzione COUNTIF di Excel (con un unico criterio)

La funzione COUNTIF di Excel funziona meglio quando si desidera contare le celle in base a un unico criterio.Se vuoi contare in base a più criteri, usa la funzione COUNTIFS.

sintassi

=COUNTIF(intervallo, condizione)

Parametri di input

• scopo -  L'intervallo di celle che vuoi calcolare.
• condizione -Criteri che devono essere valutati rispetto all'intervallo di celle da contare.

Funzione COUNTIFS di Excel (con più criteri)

La funzione COUNTIFS di Excel funziona meglio quando si desidera contare le celle in base a più criteri.

sintassi

=COUNTIFS(criteri_intervallo1,criteri1,[criteri_intervallo2,criteri2]...)

Parametri di input

• intervallo_criteri1 – L'intervallo di celle che si desidera valutare rispetto al criterio 1.
• criteri 1 – Il criterio che si desidera valutare è criterio_range1 per determinare quali celle valutare.
• [intervallo_criteri2] – L'intervallo di celle che si desidera valutare rispetto al criterio 2.
• [criterio2] – Il criterio che si desidera valutare è criterio_range2 per determinare quali celle contare.

Ora diamo un'occhiata ad alcuni esempi di utilizzo di più criteri nella funzione CONTA.SE di Excel.

Utilizzare la condizione NUMERO nella funzione COUNTIF di Excel

#1 Conta le celle quando il criterio è uguale a un valore

Per ottenere il conteggio delle celle in cui il parametro condition è uguale al valore specificato, è possibile immettere direttamente la condizione o utilizzare un riferimento di cella che contenga la condizione.

Di seguito è riportato un esempio in cui contiamo le celle che contengono il numero 9 (il che significa che il parametro condition è uguale a 9).Ecco la formula:

=CONTA.SE($B$2:$B$11,D3)

Nell'esempio sopra (nell'immagine), il criterio è nella cella D3.Puoi anche inserire le condizioni direttamente nelle formule.Ad esempio, puoi anche utilizzare:

=CONTA.SE($B$2:$B$11,9)

#2 Conta le celle quando la condizione è maggiore del valore

Per ottenere il conteggio delle celle con un valore maggiore di un valore specificato, utilizziamo l'operatore maggiore di (">").Possiamo usarlo direttamente nelle formule o possiamo usare riferimenti di cella con condizioni.

Ogni volta che utilizziamo un operatore in una condizione in Excel, dobbiamo inserirlo tra virgolette doppie.Ad esempio, se la condizione è maggiore di 10, allora dobbiamo inserire ">10" come condizione (vedi immagine sotto):

Ecco la formula:

=COUNTIF($B$2:$B$11,”>10”)

Puoi anche includere condizioni nelle celle e utilizzare i riferimenti di cella come condizioni.In questo caso non è necessario mettere la condizione tra virgolette:

=CONTA.SE($B$2:$B$11,D3)

Questo può verificarsi anche quando si desidera che la condizione sia in una cella ma non si desidera utilizzarla con un operatore.Ad esempio, potresti volere che il numero nella cella D3 sia 10 anziché >10.

In questo caso, devi creare un parametro condizionale, che è una combinazione di operatore e riferimento di cella (vedi immagine sotto):

=COUNTIF($B$2:$B$11,”>”&D3)

Nota: quando si combinano operatori e riferimenti di cella, gli operatori sono sempre racchiusi tra virgolette.Operatori e riferimenti di cella sono collegati da una e commerciale (&).

# 3 Conta le celle quando il criterio è inferiore a un valore

Per ottenere il conteggio delle celle con un valore inferiore a un valore specificato, utilizziamo l'operatore minore di ("<").Possiamo usarlo direttamente nelle formule o possiamo usare riferimenti di cella con condizioni.

Ogni volta che utilizziamo un operatore in una condizione in Excel, dobbiamo inserirlo tra virgolette doppie.Ad esempio, se il criterio è che la quantità deve essere inferiore a 5, allora dobbiamo inserire "<5" come criterio (vedi immagine sotto):

=COUNTIF($B$2:$B$11,”<5”)

Puoi anche includere condizioni nelle celle e utilizzare i riferimenti di cella come condizioni.In questo caso non è necessario mettere lo standard tra virgolette (vedi immagine sotto):

=CONTA.SE($B$2:$B$11,D3)

Inoltre, potresti volere che la condizione sia nella cella, ma non con l'operatore.Ad esempio, potresti volere che la cella D3 abbia il numero 5 invece di <5.

In questo caso è necessario creare un parametro condizionale che sia una combinazione di operatore e riferimento di cella:

=COUNTIF($B$2:$B$11,"<"&D3)

Nota: quando si combinano operatori e riferimenti di cella, gli operatori sono sempre racchiusi tra virgolette.Operatori e riferimenti di cella sono collegati da una e commerciale (&).

#4 Conta le celle con più criteri - tra due valori

Per ottenere il conteggio dei valori tra due valori, dobbiamo utilizzare più condizioni nella funzione CONTA.SE.

Ecco due modi per farlo:

Metodo XNUMX: utilizzare la funzione CONTA.SE

La funzione COUNTIFS può gestire più condizioni come argomenti e contare le celle solo se tutte le condizioni sono VERE.Per contare le celle con valori compresi tra due valori specificati, come 5 e 10, possiamo utilizzare la seguente funzione COUNTIFS:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Nota: la formula sopra non conta le celle che contengono 5 o 10.Se vuoi includere queste celle, usa gli operatori maggiore o uguale a (>=) e minore o uguale a (<=).Ecco la formula:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Puoi anche includere queste condizioni nelle celle e utilizzare i riferimenti di cella come condizioni.In questo caso non è necessario mettere lo standard tra virgolette (vedi immagine sotto):

Puoi anche utilizzare una combinazione di riferimenti di cella e operatori (immettendo gli operatori direttamente nelle formule).Quando si combinano operatori e riferimenti di cella, gli operatori sono sempre racchiusi tra virgolette.Operatori e riferimenti di cella sono collegati da una e commerciale (&).

Metodo 2: utilizzare due funzioni COUNTIF

Se hai più condizioni, puoi utilizzare COUNTIFS o creare una combinazione di funzioni COUNTIFS.La seguente formula farà la stessa cosa:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

Nella formula sopra, troviamo prima il numero di celle con un valore maggiore di 5 e quindi sottraiamo il numero di celle con un valore maggiore di 10.Questo ci darà un risultato di 5 (cioè con valori maggiori di 5 e minori o uguali a 10).

Se vuoi che la formula contenga sia 5 che 10, usa invece la seguente formula:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Se vuoi che la formula escluda "5" e "10" dal conteggio, usa la seguente formula:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

È possibile includere queste condizioni nelle celle e utilizzare riferimenti di cella oppure utilizzare una combinazione di operatori e riferimenti di cella.

Utilizzare le condizioni TESTO nelle funzioni di Excel

#1 Conta le celle quando la condizione è uguale al testo specificato

Per contare le celle che contengono una corrispondenza esatta con un testo specificato, possiamo semplicemente usare quel testo come condizione.Ad esempio, in un set di dati (mostrato di seguito), se volessi contare tutte le celle denominate Joe, potrei utilizzare la seguente formula:

=COUNTIF($B$2:$B$11,"Joe")

Poiché si tratta di una stringa di testo, è necessario inserire la condizione del testo tra virgolette.

Puoi anche includere una condizione in una cella e quindi utilizzare quel riferimento di cella (come mostrato di seguito):

=CONTA.SE($B$2:$B$11;E3)

Nota: potresti ottenere risultati errati se sono presenti spazi iniziali/finali nella condizione o nell'intervallo di condizioni.Assicurati di pulire i dati prima di utilizzare queste formule.

#2 Conta le celle quando la condizione non è uguale al testo specificato

Simile a quanto visto nell'esempio sopra, puoi anche contare le celle che non contengono il testo specificato.Per fare ciò, dobbiamo usare l'operatore non uguale (<>).

Supponendo che tu voglia contare tutte le celle che non contengono il nome JOE, ecco una formula che può farlo:

=COUNTIF($B$2:$B$11,”<>Joe”)

Puoi anche includere condizioni nelle celle e utilizzare i riferimenti di cella come condizioni.In questo caso non è necessario mettere lo standard tra virgolette (vedi immagine sotto):

=CONTA.SE($B$2:$B$11;E3)

Questo può verificarsi anche quando si desidera che la condizione sia in una cella ma non si desidera utilizzarla con un operatore.Ad esempio, potresti volere che la cella D3 abbia il nome Joe invece di <>Joe.

In questo caso, devi creare un parametro condizionale, che è una combinazione di operatore e riferimento di cella (vedi immagine sotto):

=COUNTIF($B$2:$B$11,”<>”&E3)

Quando si combinano operatori e riferimenti di cella, gli operatori sono sempre racchiusi tra virgolette.Operatori e riferimenti di cella sono collegati da una e commerciale (&).

Utilizzo delle condizioni DATE nelle funzioni COUNTIF e COUNTIFS di Excel

Excel memorizza le date e le ore come numeri.Quindi possiamo usarlo come un numero.

#1 Conta le celle quando la condizione è uguale alla data specificata

Per ottenere il conteggio delle celle che contengono una data specifica, utilizzeremo l'operatore uguale (=) insieme alla data.

Per lavorare con le date, consiglio di utilizzare la funzione DATA, poiché elimina ogni possibilità che il valore della data sia errato.Quindi, ad esempio, se volessi utilizzare la data 2015 settembre 9, potrei utilizzare la funzione DATA in questo modo:

= DATA (2015,9,1)

Nonostante le differenze regionali, questa formula restituirà la stessa data.Ad esempio, 01-09-2015 sarebbe il 2015 settembre 9 con la sintassi della data statunitense e il 1 gennaio 2015 con la sintassi della data britannica.Tuttavia, questa formula tornerà sempre il 2105 settembre 9.

Ecco la formula per contare il numero di celle contenenti la data 02-09-2015:

=COUNTIF($A$2:$A$11;DATA(2015,9,2;XNUMX;XNUMX))

#2 Conta le celle quando la condizione è prima o dopo la data specificata

Per contare le celle che contengono date precedenti o successive a una data specificata, possiamo utilizzare operatori minore/maggiore di.

Ad esempio, se volessi contare tutte le celle contenenti date successive al 2015 settembre 9, potrei utilizzare la seguente formula:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))

Allo stesso modo, puoi anche contare il numero di celle prima di una data specificata.Se vuoi includere le date nel conteggio, usa l'operatore "uguale a" insieme agli operatori "maggiore di/minore di".

Puoi anche utilizzare riferimenti di cella che contengono date.In questo caso, è necessario utilizzare una e commerciale (&) per combinare l'operatore (tra virgolette doppie) con la data.

Vedi l'esempio qui sotto:

=COUNTIF($A$2:$A$11,”>”&F3)

# 3 Conta le celle con più criteri - tra due date

Per ottenere il conteggio dei valori tra due valori, dobbiamo utilizzare più condizioni nella funzione CONTA.SE.

Possiamo farlo in due modi: una singola funzione COUNTIFS o due funzioni COUNTIFS.

Metodo XNUMX: utilizzare la funzione CONTA.SE

La funzione CONTA.SE può accettare più condizioni come argomenti e contare le celle solo se tutte le condizioni sono VERE.Per contare le celle con valori compresi tra due date specificate, come il 9 settembre e il 2 settembre, possiamo utilizzare la seguente funzione COUNTIFS:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

La formula precedente non conta le celle che contengono la data specificata.Se vuoi includere anche queste date, usa gli operatori maggiore o uguale a (>=) e minore o uguale a (<=).Ecco la formula:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

Puoi anche includere date nelle celle e utilizzare i riferimenti di cella come criteri.In questo caso, non puoi fare in modo che l'operatore includa la data nella cella.È necessario aggiungere manualmente gli operatori alla formula (racchiusa tra virgolette doppie) e utilizzare una e commerciale (&) per aggiungere riferimenti di cella.Vedi sotto:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

Metodo 2: utilizzare la funzione CONTA.SE

Se hai più condizioni, puoi utilizzare una funzione COUNTIFS o creare una combinazione di due funzioni COUNTIFS.Anche la seguente formula farà il trucco:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Nella formula sopra, troviamo prima il numero di celle la cui data è successiva al 9 settembre, quindi sottraiamo il numero di celle la cui data è successiva al 2 settembre.Questo darà un risultato di 9 (ovvero, il numero di celle con date successive al 7 settembre e precedenti al 7 settembre).

Se non vuoi che la formula calcoli sia il 9 settembre che il 2 settembre, usa invece la seguente formula:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Se vuoi escludere due date dal conteggio, usa la seguente formula:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Inoltre, puoi impostare date standard nelle celle e utilizzare riferimenti di cella (e operatori tra virgolette doppie con e commerciale).

Utilizzo di caratteri jolly nelle condizioni delle funzioni COUNTIF e COUNTIFS

Ci sono tre caratteri jolly in Excel:

1. *(Asterisco) - rappresenta un numero qualsiasi di caratteri.Ad esempio, ex* potrebbe significare excel, excel, esempio, esperto, ecc.
2. ? (punto interrogativo) - Rappresenta un singolo carattere.Ad esempio, Trömp potrebbe significare Trump o Tramp.
3. ~ (tilde) - Utilizzato per identificare i caratteri jolly (~, *, ?) nel testo.

È possibile utilizzare la funzione CONTA.SE con i caratteri jolly per contare le celle quando altre funzioni di conteggio integrate falliscono.Ad esempio, supponiamo di avere un set di dati simile a questo:

Ora facciamo alcuni esempi:

# 1 Conta le celle che contengono testo

Per contare le celle con del testo, possiamo usare il carattere jolly * (asterisco).Poiché gli asterischi rappresentano un numero qualsiasi di caratteri, conterà tutte le celle che contengono testo.Ecco la formula:

=COUNTIFS($C$2:$C$11,"*")

Nota: la formula precedente ignora le celle che contengono numeri, celle vuote e valori logici, ma conta le celle che contengono apostrofi (e quindi appaiono vuote) o che potrebbero essere state incluse come parte della formula.

Ecco un tutorial dettagliato sulla gestione dei casi con stringhe vuote o apostrofi.

# 2 Conta le celle non vuote

Se stai pensando di utilizzare la funzione COUNTA, ripensaci.

Provalo, potrebbe deluderti.COUNTA conterà anche le celle che contengono stringhe vuote (di solito restituite da formule come ="" o quando le persone inseriscono semplicemente apostrofi nelle celle).Le celle che contengono stringhe vuote sembrano vuote ma non lo sono, quindi vengono conteggiate dalla funzione CONTA.

COUNTA conterà anche le celle che contengono stringhe vuote (di solito restituite da formule come ="" o quando le persone inseriscono semplicemente apostrofi nelle celle).Le celle che contengono stringhe vuote sembrano vuote ma non lo sono, quindi vengono conteggiate dalla funzione CONTA.

Quindi se usi la formula = COUNTA (A1:A11), restituisce 11 quando dovrebbe restituire 10.

Ecco la correzione:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

Comprendiamo questa formula scomponendola:

# 3 Conta le celle contenenti testo specifico

Supponiamo di voler contare tutte le celle in cui il nome del rappresentante di vendita inizia con J.Questo può essere ottenuto facilmente utilizzando i caratteri jolly nella funzione COUNTIF.Ecco la formula:

=COUNTIFS($C$2:$C$11,"J*") 

La condizione J* specifica che il testo nella cella deve iniziare con J e può contenere un numero qualsiasi di caratteri.

Se vuoi contare le celle che contengono lettere in qualsiasi punto del testo, circondale con un asterisco.Ad esempio, se vuoi contare le celle che contengono la lettera "a", usa *a* come condizione.

Rispetto agli altri miei articoli, questo è insolitamente lungo.Spero ti piaccia.Fatemi sapere cosa ne pensate lasciando un commento.