Conta valori univoci in base a valore ripetuto in altra colonna

Ciao a tutti,

Avrei da contare i valori univoci (numeri e testo) nella colonna E con anche celle vuote. Questo SE nelle celle della colonna A è presente o meno un dato testo.

Per i valori univoci ero arrivato a questa formula, ma non saprei come inserire la condizione.

=MATR.SOMMA.PRODOTTO((E2:E7000<>"")/CONTA.SE(E2:E7000;E2E7000&""))


Grazie

Buonasera
allega un file con u po di dati e il risultato da ottenere scritto a mano

Grazie dell'interessamento. Allego un estratto che dovrebbe essere esplicativo della formula che necessito.

Ciao.
in C2 foglio categorie:
formula matriciale:

=SOMMA(SE(A2=Dati!$A$2:$A$5000;1/CONTA.PIÙ.SE(Dati!$A$2:$A$5000;A2;Dati!$B$2:$B$5000;Dati!$B$2:$B$5000)))

xam

Grazie Xam99,
ho provato a inserire la formula in C2, ma restituisce il valore 1 invece di 3

Ciao,
è una formula matriciale da inserire con la combinazione di tasti:
CTRL+SHIFT+ENTER (INVIO)
xam

Buona sera

una alternativa

c2=SOMMA(SE(FREQUENZA(SE(Dati!$A$2:$A$7000<>"";CONFRONTA(Dati!$A$2:$A$7000;Dati!$A$2:$A$7000;0));RIF.RIGA(Dati!$A$2:$A$7000)-RIF.RIGA(Dati!$A$2)+1);1))

control+shift+enter

Ok, adesso funziona... Grazie
Ho provato ad adattarla al documento originale, 2 note:
- in 2 colonne (su 5) talvolta mi restituisce errore #DIV/0; però ho visto che mi dà l'errore anche se per data categoria le celle non sono tutte vuote
- tra poco mi si fonde il PC ed excel è impallato a calcolare; in effetti le categorie sono più di 80 ed i record per colonna oltre 40.000
Mi sa che conviene usare una macro avviandola ogni tot per aggiornare i valori, ma inizia a diventare complicato; in base all'excel che ho sarebbe:

se nella colonna A in 'Categorie!' da A2 in giù (fino a cella con valore, ovvero la categoria), nelle celle D2, E2, G2, H2, I2 aggiornare il calcolo di cui sopra in riferimento alla colonna A in 'Dati!' da A2 in giù (fino a cella con valore, ovvero dove ci sono le categorie ripetute) e colonne D,E,G,H,I sempre in 'Dati!' (dove ci sono le celle non vuote da contare univoche).
Nel caso allego l'excel con i riferimenti completi e casi con celle tutte vuote, numeri o testo.

Mi sparo...

Ciao
per un macro che faccia come la formula matriciale deve intervenire
un mago delle macro.
Un saluto.
xam

ci potrei provare, ma non ho capito bene qual'è il risultato desiderato, lo puoi allegare ?

Ciao Patel45,
nel mio post sopra ho allegato l'excel originale con i riferimenti delle celle in base a quanto ho scritto per la macro; per ogni colonna di interesse (D2,E2,G2,H2,I29) nel foglio Dati! ho messo varie combinazioni di valori e nelle rispettive nel foglio Categorie! ci sono i risultati in rosso attesi.

Grazie xam99,
non avevo calcolato tutto questo calcolo necessario. In fondo Excel la scrematura dei duplicati la fa in un attimo anche con tanti record; qui in fondo si tratta di contare i risultati univoci che si presentano se nella cella in A c'è un dato valore. Non so se sbaglio io per il #DIV/0, ma compare anche in alcune prove con il nuovo excel.
Per la macro adesso provo a ricercare, di certo non sarò in grado di modificarla per adattarla a tutte le celle e colonne.

Grazie Ulisse1990,
il risultato veniva giusto in C2, però non riesco a capire come spostarla (con xam99 vedo chiaramente il riferimenti alla cella A2 e colonna B)

nel foglio dati non capisco i risultati della colonna D

patel45, 9/24/2017 2:09 PM:

---

nel foglio dati non capisco i risultati della colonna D

---

Se ho capito, nel foglio Dati!:
- per la catetoria1 i valori univoci nella colonna D sono A (che si ripete) e B, quindi 2
- per la catetoria2 nella colonna D c'è solo A che si ripete, quindi 1
- per la catetoria3 nella colonna D ci sono A, B e C, quindi 3

In pratica è un elenco di contatti, in queste 4 colonne da cui vorrei contare gli univoci c'è di tutto: numeri, testo, solo celle vuote, ecc; talvolta i valori si ripetono nella colonna.
La formula dovrebbe considerare le celle non vuote e restituire la conta degli univochi sulla base della categoria; quella di xam99 è il punto di partenza, non so se è omnicomprensiva di tutte le situazioni o c'è una soluzione con meno dispendio di calcolo.

La macro finchè trova un valore nella colonna A nel foglio Categorie! (da A2), dovrebbe restituire i risultati dalle celle D2,E2,G2,H2, in giù andando a ricercare nelle rispettive colonne del foglio Dati! sempre fino all'ultima riga con valore.
In tal modo se per caso aggiungessi una categoria non dovrei preoccuparmi di inserire formule; stessa cosa per gli intervalli nel caso di nuovi recordi.

Macro permettendo, ho trovato ed adattato al foglio la seguente matriciale che mi riporta i valori corretti e sembra non intasarmi la CPU; in D2 e si trascina sotto e a destra:

=SOMMA(SE(FREQUENZA(SE((Dati!$A$2:$A$50000=$A2);CONFRONTA(Dati!D$2:D$50000;Dati!D$2:D$50000;0);"");SE((Dati!$A$2:$A$50000=$A2);CONFRONTA(Dati!D$2:D$50000;Dati!D$2:D$50000;0);""))>0;1))

L'unico (spero) problema è che non contempla le celle vuote, dove presenti mi restituisce errore di valore non disponibile #N/D.

Ciao
per visualizzare errore #N/D si può provare questa formula:

=se(val.errore( TUA FORMULA );0; TUA FORMULA )

sostituisci la parte TUA FORMULA con la tua formula.
xam

Ciao,
fatto. Non cambia nulla nei risultati che già venivano calcolati; dove invece compariva l'errore ora compare sempre 0. C'è da gestire l'errore a monte con le celle vuote credo. Tipo &"" o <>"" da qualche parte per l'intervallo nella colonna D.

Ops
è questa:

=se(val.errore( TUA FORMULA );""; TUA FORMULA )

xam

Provato. Adesso le celle incriminate sono 'vuote'.

Naturalmente dentro alle virgolette:

=se(val.errore( TUA FORMULA );" ciao ciao "; TUA FORMULA )

puoi mettere quello che vuoi.
xam

Sì, ma io devo calcolare gli univoci. L'errore mi viene perchè ci sono celle vuote nell'intervallo (ma ci sono anche valori) e la formula non le contempla.

Stavo appunto leggendo online in casi simili come hanno gestito le celle vuote, tipo un'ulteriore condizione con <>"":

=SOMMA(--(FREQUENZA(SE(B3:B10<>"";SE(C3:C10=G3;CONFRONTA(B3:B10;B3:B10;0)));RIF.RIGA(B3:B10)-RIF.RIGA(B3)+1)>0))

In caso di errori per celle vuote nel file allegato ci sono queste 2 formule:

=SE(( TUA FORMULA )<>0; TUA FORMULA ;"")

=SE(VAL.ERRORE( TUA FORMULA );""; TUA FORMULA )

e con celle vuote funziona tutto.
xam

Grazie xam99,
dovrei avere trovato in contemporanea quella finale sull'esempio appena sopra senza passaggi; in D2 (ctrl+shift+invio) e si trascina sotto e a destra:

=SOMMA(--(FREQUENZA(SE(Dati!D$2:D$50000<>"";SE(Dati!$A$2:$A$50000=$A2;CONFRONTA(Dati!D$2:D$50000;Dati!D$2:D$50000;0)));RIF.RIGA(Dati!D$2:D$50000)-RIF.RIGA(D$2)+1)>0))

La macro è oltre le mie possibilità, ma è già un passo avanti.

Ciao per la macro qui ci soo dei maghi.
xam

prova questa

Grazie patel45,
se ho ben capito posso dirti fin da subito che non è possibile avere valori uguali e consecutivi per tutte le colonne: un contatto potrebbe comparire in più province ad esempio, ma anche ordinare per stesse categorie in A porta ad avere alcune info di indirizzo che si ripetono ogni categoria.
Intanto la copio per i riferimenti delle celle

Inserita nel foglio dopo aver visto che hai tolto il riferimento ai valori uguali consecutivi e... Funziona!!! Sopratutto ci mette un attimo anche se opera su tutte le colonne (meglio così).
Ma c'è una così alta differenza in prestazioni tra macro e formule o è l'approccio usato per il calcolo?
Comunque grazie!

con le macro si può fare tutto, basta trovare la giusta strategia ed in questo caso ho dovuto faticare, lo si vede dalla struttura piuttosto contorta. Con le formule si possono fare cose semplici e questo caso non lo è.