Formula generica
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Sommario
Per contare valori univoci con una o più condizioni, è possibile utilizzare una formula basata su UNICO e FILTRO. Nell'esempio mostrato, la formula in H7 è:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
che restituisce 3, poiché ci sono tre nomi univoci in B6: B15 associati al progetto Omega.
Nota: questa formula richiede formule array dinamiche, disponibili solo in Excel 365. Con una versione precedente di Excel, è possibile utilizzare formule alternative più complesse.
Spiegazione
Fondamentalmente, questa formula utilizza la funzione UNIQUE per estrarre valori univoci e la funzione FILTER applica i criteri.
Operando dall'interno verso l'esterno, la funzione FILTRO viene utilizzata per applicare criteri ed estrarre solo i nomi associati al progetto "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Il risultato di FILTER è un array come questo:
("Jim";"Jim";"Carl";"Sue";"Carl")
Successivamente, la funzione UNIQUE viene utilizzata per rimuovere i duplicati:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
che si traduce in un nuovo array come questo:
("Jim";"Carl";"Sue") // after UNIQUE
A questo punto, abbiamo un elenco univoco di nomi associati a Omega e dobbiamo solo contarli. Per i motivi spiegati di seguito, lo facciamo con la funzione LEN e la funzione SUM. Per chiarire le cose, prima riscriveremo la formula per includere l'elenco univoco:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
La funzione LEN ottiene la lunghezza di ogni elemento nell'elenco e restituisce un array di lunghezze:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Successivamente, controlliamo se le lunghezze sono maggiori di zero:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
E usa un doppio negativo per forzare i valori VERO e FALSO su 1 e 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Infine, sommiamo i risultati con la funzione SUM:
=SUM((1;1;1)) // returns 3
Questo array viene consegnato direttamente alla funzione COUNTA, che restituisce un conteggio finale:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Tieni presente che poiché stiamo controllando la lunghezza di ogni elemento restituito da UNIQUE, le celle vuote o vuote che soddisfano i criteri vengono ignorate. Questa formula è dinamica e verrà ricalcolata immediatamente se i dati di origine vengono modificati.
Conta unico con più criteri
Per contare valori univoci in base a più criteri, è possibile estendere la logica "include" all'interno di FILTER. Ad esempio, per contare i nomi univoci per il progetto Omega solo a giugno, utilizzare:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Questo è un esempio dell'utilizzo della logica booleana per applicare più di una condizione. L'approccio è spiegato più dettagliatamente qui.
Per maggiori dettagli, guarda questo video di formazione: Come filtrare con più criteri.
COUNTA
È possibile scrivere una formula più semplice che risponda sulla funzione CONTA.VALORI. Tuttavia, un avvertimento importante è che COUNTA restituirà 1 quando non ci sono valori corrispondenti. Questo perché la funzione FILTER restituisce un errore quando nessun dato corrisponde ai criteri e questo errore finisce per essere conteggiato dalla funzione COUNTA. La formula di base COUNTA ha questo aspetto:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Anche in questo caso, questa formula restituirà 1 quando non ci sono dati corrispondenti. Includerà anche celle vuote che soddisfano i criteri. La formula basata su LEN e SUM è un'opzione migliore.
Nessun array dinamico
Se stai utilizzando una versione precedente di Excel senza supporto per array dinamici, puoi utilizzare una formula più complessa. Per una discussione più generale sulle alternative di array dinamici, vedere: Alternatives to Dynamic Array Formulas.
