SOMMA.PIÙ.SE e CONTA.PIÙ.SE: sommare e contare con più condizioni

Sapere quanto ha venduto in totale un venditore è facile. Il difficile arriva quando la domanda ha più parti: quanto ha venduto Ana nella zona Nord, quanti ordini sopra 200 ci sono stati a marzo o quante volte compare lo stesso prodotto per lo stesso venditore. A questo servono SOMMA.PIÙ.SE e CONTA.PIÙ.SE.

Le due funzioni applicano contemporaneamente tutte le condizioni che indichi e considerano solo le righe che le soddisfano tutte. Vedrai la sintassi, esempi con testo, numeri, date e caratteri jolly, come costruire una tabella di riepilogo e come usarle per individuare i record ripetuti.

I dati di esempio

Useremo questo registro delle vendite, con le intestazioni nella riga 1 e i dati nelle righe da 2 a 9. La colonna A contiene date vere, non testo:

Sintassi delle due funzioni

Sono entrambe disponibili da Excel 2007 ed esistono anche in Fogli Google e LibreOffice Calc.

=SOMMA.PIÙ.SE(int_somma;int_criteri1;criteri1;int_criteri2;criteri2)

=CONTA.PIÙ.SE(int_criteri1;criteri1;int_criteri2;criteri2)

• int_somma: le celle con i numeri da sommare (solo in SOMMA.PIÙ.SE).
• int_criteri: la colonna in cui viene verificata ogni condizione.
• criteri: la condizione, ad esempio "Ana", ">=200" o un riferimento di cella.

Puoi aggiungere fino a 127 coppie di intervallo e criterio. Attenzione a un dettaglio che confonde molte persone: in SOMMA.SE l’intervallo da sommare va alla fine, mentre in SOMMA.PIÙ.SE va all’inizio.

Sommare e contare con più condizioni

Per sommare le vendite di Ana nella zona Nord:

=SOMMA.PIÙ.SE(E2:E9;B2:B9;"Ana";C2:C9;"Nord")

Il risultato è 520: contano solo le righe 2 e 4. La vendita di Ana nella zona Sud resta esclusa. Per contare quante vendite del Prodotto A ci sono state nella zona Nord:

=CONTA.PIÙ.SE(C2:C9;"Nord";D2:D9;"Prodotto A")

Restituisce 3 (righe 2, 5 e 9).

Criteri con operatori

Per confrontare numeri, scrivi l’operatore e il valore insieme tra virgolette. Questa formula conta le vendite della zona Nord pari o superiori a 200:

=CONTA.PIÙ.SE(E2:E9;">=200";C2:C9;"Nord")

Il risultato è 4. Gli operatori validi sono =, <>, >, <, >= e <=. Ad esempio, "<>Sud" conta tutto ciò che non è Sud.

Criteri con le date

Per sommare un periodo, metti la data iniziale in K1 e quella finale in K2 e unisci l’operatore alla cella con &:

=SOMMA.PIÙ.SE(E2:E9;A2:A9;">="&K1;A2:A9;"<="&K2)

Con il 1° e il 31 marzo in quelle celle, il risultato è 1670. Nota che lo stesso intervallo compare due volte, una per ogni limite. Usare le celle invece di scrivere la data nella formula evita problemi con il formato data di ciascun paese.

Caratteri jolly

Nei criteri di testo puoi usare * (qualsiasi numero di caratteri) e ? (un solo carattere). Per contare le vendite dei venditori il cui nome inizia con M:

=CONTA.PIÙ.SE(B2:B9;"M*")

Restituisce 2. Se devi cercare un vero asterisco, scrivilo come ~*.

Creare una tabella di riepilogo

L’applicazione più utile è costruire un piccolo report incrociato. Scrivi i venditori in G2:G4, le zone in H1 (Nord) e I1 (Sud) e in H2 questa formula:

=SOMMA.PIÙ.SE($E$2:$E$9;$B$2:$B$9;$G2;$C$2:$C$9;H$1)

Poi copiala nel resto della tabella. I simboli $ sono la chiave:

1. Gli intervalli di dati hanno il $ su riga e colonna, così non si spostano durante la copia.
2. $G2 blocca la colonna: ogni riga della tabella legge sempre il suo venditore.
3. H$1 blocca la riga: ogni colonna legge sempre la sua zona.
4. Premi F4 più volte su un riferimento per passare da un tipo di blocco all’altro.

La somma dell’intera tabella (2110) coincide con il totale della colonna E: un controllo veloce che nessuna riga sia rimasta fuori.

Quando serve una condizione O

Tutte le condizioni di queste funzioni si combinano con E: la riga deve soddisfarle tutte. Se vuoi sommare le vendite di Ana o di Luis, il modo più chiaro è sommare due formule:

=SOMMA.PIÙ.SE(E2:E9;B2:B9;"Ana")+SOMMA.PIÙ.SE(E2:E9;B2:B9;"Luis")

Il risultato è 1520 (1020 di Ana più 500 di Luis). Fai solo attenzione che le condizioni non si sovrappongano: se una riga potesse soddisfarle entrambe, verrebbe sommata due volte.

Individuare i duplicati con CONTA.PIÙ.SE

CONTA.PIÙ.SE serve anche a sapere quante volte compare una combinazione di valori. In una colonna di appoggio, scrivi nella riga 2:

=SE(CONTA.PIÙ.SE($B$2:$B$9;B2;$D$2:$D$9;D2)>1;"Ripetuto";"")

La formula conta quante righe hanno lo stesso venditore e lo stesso prodotto della riga corrente. Nei dati di esempio segna come ripetute le coppie Ana e Prodotto A (righe 2 e 7) e Luis e Prodotto A (righe 6 e 9). È utile per controllare prima di eliminare: puoi filtrare la colonna di appoggio e decidere cosa fare in ogni caso.

Se vuoi semplicemente eliminare le righe ripetute da un file grande, il nostro strumento gratuito per rimuovere i duplicati lo fa con file XLSX, XLS, CSV e ODS, senza formule.

Errori comuni

• Intervalli di dimensioni diverse. Se l’intervallo da sommare è E2:E9 e un intervallo di criteri è B2:B8, Excel restituisce #VALORE!. Devono avere tutti lo stesso numero di righe.
• Operatore non concatenato. ">=K1" confronta con il testo K1, non con il valore della cella. La forma corretta è ">="&K1.
• Numeri memorizzati come testo nell’intervallo da sommare. Vengono ignorati, quindi il totale risulta più basso del previsto. Succede spesso con dati incollati da altri programmi o importati da CSV.
• Cartelle di lavoro esterne chiuse. Se gli intervalli si trovano in un altro file e quel file è chiuso, la formula restituisce #VALORE!. Aprilo oppure copia i dati nella stessa cartella di lavoro.
• Spazi in più. “Ana ” con uno spazio finale non corrisponde ad “Ana”. Pulisci la colonna con ANNULLA.SPAZI prima di calcolare.

Conclusione

SOMMA.PIÙ.SE e CONTA.PIÙ.SE rispondono a domande con più condizioni senza tabelle pivot né filtri manuali. Con criteri nelle celle, riferimenti misti e l’operatore & puoi creare report che si aggiornano da soli quando aggiungi dati.

Mantieni gli intervalli della stessa dimensione, verifica i totali con una somma generale e pulisci i dati prima di iniziare: la maggior parte dei risultati strani deriva da spazi, testi o righe ripetute.

Domande frequenti