Contare in base a più condizioni con COUNTIFS

COUNTIFS conta quante righe soddisfano diverse condizioni contemporaneamente. Mentre COUNTIF risponde alla domanda "quante vendite sono state effettuate nella regione Est?", COUNTIFS risponde a "quante vendite della regione Est sono state effettuate a gennaio?"

Non somma né calcola medie: conta semplicemente le righe che superano ogni test. È quindi perfetto per domande come "quanti ordini superiori a 100 $ provengono da nuovi clienti?"

Vediamo la sua struttura e impariamo a utilizzarla.

COUNTIFS non ha un sum_range, perché non somma valori: ha bisogno solo degli intervalli da verificare e dei criteri con cui eseguire la verifica.

• criteria_range1, criteria1 — il primo test
• criteria_range2, criteria2 — il secondo test
• ... e così via, fino a 127 coppie

Ogni argomento viene indicato nella forma ordinata intervallo, criterio. Il risultato è un unico numero intero: il conteggio delle righe in cui tutte le condizioni sono vere.

Utilizzando la stessa tabella delle vendite — la colonna A contiene la Regione, la colonna B il Mese e la colonna C l'Importo — contiamo quante vendite sono state effettuate nella regione Est a gennaio.

In questa formula non compare alcuna colonna degli importi, perché contiamo le righe invece di sommare il denaro. Ogni coppia restringe ulteriormente il conteggio.

Proprio come SUMIFS, COUNTIFS accetta gli operatori di confronto tra virgolette per i test numerici.

• ">100" — valori superiori a 100
• ">=18" — 18 o più
• "<>" — qualsiasi cella non vuota

Qui contiamo le righe della regione Est in cui l'importo è maggiore di 100. Noti che la colonna degli importi compare come criteria_range, non come sum_range.

Spesso è necessario contare i valori che rientrano tra due numeri. Non è possibile farlo con un solo criterio: occorre indicare due volte la stessa colonna, specificando un limite inferiore e uno superiore.

In questo modo si contano gli importi pari ad almeno 50 e al massimo a 200. Entrambe le condizioni fanno riferimento alla colonna C e, poiché COUNTIFS utilizza la logica AND, vengono contate solo le righe comprese nell'intervallo.

Per rendere dinamici i limiti, memorizzare i valori nelle celle e unire l'operatore con &. Inserisca il valore minimo in F1 e quello massimo in F2.

">="&F1 costruisce il criterio maggiore o uguale a F1. In questo modo l'utente può inserire nuovi limiti e visualizzare immediatamente il conteggio aggiornato senza modificare la formula.

Due criteri speciali sono utili per i controlli della qualità dei dati:

• "<>" conta le celle non vuote
• "=" o "" conta le celle vuote

Questa formula conta quante righe della regione Est hanno un venditore compilato, cioè quante celle della colonna D non sono vuote: è un modo rapido per individuare i dati mancanti.

I criteri testuali supportano gli stessi caratteri jolly di SUMIFS. Utilizzi * per una sequenza qualsiasi di caratteri e ? per un singolo carattere.

In questo modo si contano tutte le righe in cui il nome del venditore inizia con la lettera M: corrispondono Maria, Mike e Mohammed. I caratteri jolly agiscono solo sul testo e vengono ignorati per i numeri.

La stessa regola sulle dimensioni di SUMIFS si applica anche qui: ogni criteria_range deve avere la stessa forma. Se un intervallo è A2:A100 e un altro è B2:B50, COUNTIFS restituisce un errore #VALUE!.

I riferimenti a colonne intere, come A:A e B:B, hanno automaticamente le stesse dimensioni. Per questo sono una scelta diffusa e priva di errori per gli intervalli dei criteri.

COUNTIFS è alla base di molte dashboard. Inserisca i nomi delle regioni nella colonna F e i nomi dei mesi lungo la riga 1, quindi conti ogni combinazione utilizzando riferimenti misti, così da riempire l'intera griglia con la formula.

Bloccando le colonne con $A:$A e le celle di ricerca con $F2 / G$1, una sola formula può compilare un'intera tabella incrociata di conteggi quando viene copiata.

Impari a distinguere le funzioni della stessa famiglia. COUNTIF accetta una singola coppia range, criteria, mentre COUNTIFS ne accetta una o più. A differenza di SUMIFS, nessuna delle due ha un intervallo separato per il risultato: entrambe si limitano a contare.

Se ha più di una condizione, scelga COUNTIFS. Molte persone usano COUNTIFS anche per un solo test, così devono ricordare un unico schema coerente per SUM, COUNT e AVERAGE.

Ora può contare le righe che soddisfano numerose condizioni:

• COUNTIFS(range1, crit1, range2, crit2, ...) — non c'è un sum_range, ma solo coppie di intervallo e criterio.
• Per contare un intervallo compreso tra due valori, ripeta la stessa colonna con due limiti.
• Utilizzi "<>" per le celle non vuote e concateni con & per i limiti basati sulle celle.
• Tutti gli intervalli devono avere le stesse dimensioni.

Prossimo argomento: calcolare la media dei valori che soddisfano più condizioni con AVERAGEIFS.