#Excel – Come usare SOMMA.PIÙ.SE e SOMMA.SE con criteri multipli

Come tutti sanno, Microsoft Excel offre una serie di funzioni per eseguire diversi calcoli con i dati. Alcuni articoli fa, abbiamo esplorato CONTA.SE e CONTA.PIÙ.SE, che sono progettate rispettivamente per il conteggio delle celle sulla base di una singola condizione oppure di diverse condizioni. Poi abbiamo coperto la funzione SOMMA.SE che somma i valori che soddisfano un criterio specificato. Ora è il momento di esaminare la versione “plurale” di SOMMA.SE: SOMMA.PIÙ.SE che consente di sommare i valori che soddisfano diversi criteri.

Coloro che hanno familiarità con la funzione SOMMA.SE potrebbero pensare che la conversione a SOMMA.PIÙ.SE richieda solo un “PIÙ” extra e alcuni criteri aggiuntivi. Questo sembrerebbe abbastanza logico … ma “logico” non è sempre vero quando si ha a che fare con Microsoft 🙂

La funzione SOMMA.SE di Excel

La funzione SOMMA-SE viene utilizzata per sommare valori a patto che soddisfino un singolo criterio. Abbiamo discusso dettagliatamente la sua sintassi nell’articolo precedente, quindi lasciate che vi dia solo un breve riepilogo.

SOMMA.SE(intervallo; criterio; [int_somma])

Come potete vedere, la sintassi della funzione SOMMA.SE consente una sola condizione. Ma, allo stesso tempo, vi diciamo che SOMMA.SE può essere utilizzato per sommare valori in base a più criteri. Come può essere? Sommando i risultati di diverse funzioni SOMMA.SE e utilizzando formule SOMMA.SE con criteri matriciali, come dimostrato negli esempi che seguono.

La funzione SOMMA.PIÙ.SE di Excel

Si usa SOMMA.PIÙ.SE per trovare una somma condizionale dei valori basata su più criteri. La funzione SOMMA.PIÙ.SE è stata introdotta in Excel 2007, pertanto è possibile utilizzarla in tutte le versioni moderne di Excel 2016, 2013, 2010 e 2007.

Rispetto a SOMMA.SE, la sintassi di SOMMA.PIÙ.SE è un po’ più complessa:

SOMMA.PIÙ.SE(int_somma; intervallo_criteri1; criteri1; [intervallo_criteri2; criteri2]; ...)

I primi 3 argomenti sono obbligatori, intervalli_criteri aggiuntivi e i loro criteri associati sono facoltativi.

E adesso vediamo una formula Excel SOMMA.PIÙ.SE con due condizioni. Supponiamo di avere una tabella che elenca le consegne di frutta da diversi fornitori. Avete i nomi della frutta nella colonna A, i nomi dei fornitori nella colonna B e la quantità nella colonna C. Quello che volete è calcolare la somma delle quantità relative a un determinato frutto e fornitore, ad esempio: tutte le mele fornite da Luigi.

Quando stai imparando qualcosa di nuovo, è sempre una buona idea iniziare con cose semplici. Quindi, per cominciare, definiamo tutti gli argomenti della nostra formula SOMMA.PIÙ.SE:

Ora assemblate i parametri sopra elencati e otterrete la seguente formula SOMMA.PIÙ.SE:

=SOMMA.PIÙ.SE(C2:C9; A2:A9; "Mele"; B2:B9; "Luigi")

Per perfezionare ulteriormente la formula, è possibile sostituire i criteri testuali “Mele” e “Luigi” con i riferimenti di cella. In questo modo, non dovrete cambiare la formula per calcolare la quantità di frutta fornita da un altro fornitore:

=SOMMA.PIÙ.SE(C2:C9; A2:A9; F1; B2:B9; F2)

Usare SOMMA.SE e SOMMA.PIÙ.SE in Excel – Alcuni punti di attenzione

Poiché l’obiettivo di questo tutorial è quello di coprire tutte le possibili modalità di somma dei valori con diverse condizioni, discuteremo esempi di formule con entrambe le funzioni: SOMMA.PIÙ.SE e SOMMA.SE con più criteri. Per utilizzarle correttamente, è necessario capire chiaramente cosa le due funzioni abbiano in comune e in che modo siano differenti.

Mentre le parti che hanno in comune dovrebbero essere abbastanza chiare, le differenze non sono così evidenti, anche se molto essenziali.

L’ordine degli argomenti

Nelle funzioni di Excel SOMMA.SE e SOMMA.PIÙ.SE, l’ordine degli argomenti è diverso. In particolare, int_somma è il primo parametro in SOMMA.PIÙ.SE, ma è il terzo in SOMMA.SE.

A prima vista, può sembrare che Microsoft complichi deliberatamente la curva di apprendimento per i suoi utenti. Tuttavia, con uno sguardo più attento, vedrete il ragionamento che c’è dietro. Il punto è che int_somma è opzionale in SOMMA.SE. Se lo ometti, nessun problema, la vostra formula SOMMA.SE sommerà i valori in intervallo (primo parametro).

In SOMMA.PIÙ.SE, la somma è molto importante e obbligatoria, ed è per questo che viene prima. Probabilmente i ragazzi di Microsoft hanno pensato che dopo aver aggiunto la decima o la centesima coppia di intervallo / criteri, qualcuno potrebbe dimenticare di specificare l’intervallo da sommare 🙂

Se copiate e modificate queste funzioni, assicuratevi di inserire i parametri nell’ordine giusto.

La dimensione degli argomenti int_somma e intervallo_criteri

Nella funzione SOMMA.SE, l’argomento int_somma non deve necessariamente avere la stessa dimensione dell’argomento intervallo, a patto che la cella superiore sinistra sia corretta. In SOMMA.PIÙ.SE, ciascun intervallo_criteri deve contenere lo stesso numero di righe e colonne del parametro int_somma.

Ad esempio, =SOMMA.SE(A2:A9; F1; C2:C18) restituirà il risultato corretto poiché Excel considera solo la cella superiore sinistra nell’argomento int_somma (C2 in questo esempio, che è corretto) e quindi include tante colonne righe quante sono contenute nell’argomento intervallo.

La formula SOMMA.PIÙ.SE =SOMMA.PIÙ.SE(C2:C9; A2:A9; “Mele”; B2:B10; “Luigi”) restituirà il valore d’errore #VALORE! perché intervallo_criteri2 (B2:B10) non corrisponde alla dimensione di intervallo_criteri1 (A2:A9) e int_somma (C2:C9).

Va bene, basta con la strategia (cioè la teoria), approfondiamo la tattica (cioè gli esempi di formula 🙂 )

Come usare SOMMA.PIÙ.SE in Excel – Esempi di formule

Un momento fa, abbiamo discusso una semplice formula SOMMA.PIÙ.SE con due criteri testuali. Allo stesso modo, è possibile utilizzare SOMMA.PIÙ.SE con più criteri espressi da numeri, date, espressioni logiche e altre funzioni di Excel.

Esempio 1. SOMMA.PIÙ.SE con operatori di confronto

Nella nostra tabella dei fornitori di frutta, supponiamo, di voler sommare tutte le consegne di Michele con Q.tà di 200 o più. A tale scopo, utilizziamo l’operatore di confronto “maggiore o uguale a” (>=) in criteri2 e otteniamo la seguente formula SOMMA.PIÙ.SE:

=SOMMA.PIÙ.SE(C2:C9; B2:B9; "Michele"; C2:C9; ">=200")

Abbiamo trattato in dettaglio tutti i possibili operatori di confronto quando abbiamo parlato della funzione SOMMA.SE, gli stessi operatori possono essere utilizzati nei criteri SOMMA.PIÙ.SE. Ad esempio, la seguente formula restituisce la somma di tutti i valori nelle celle C2:C9 che sono maggiori o uguali a 200 e inferiori o uguali a 300:

=SOMMA.PIÙ.SE(C2:C9; C2:C9; ">=200"; C2:C9; "<=300")

Esempio 2. SOMMA.PIÙ.SE con le date

Nel caso in cui vogliate sommare valori con criteri multipli in base alla data corrente, utilizzate la funzione OGGI() nei criteri SOMMA.PIÙ.SE come mostrato di seguito. La formula seguente somma i valori di colonna C se la data corrispondente di colonna B cade negli ultimi 7 giorni, compreso oggi:

=SOMMA.PIÙ.SE(C2:C9; B2:B9; ">="&OGGI()-7; B2:B9; "<="&OGGI())

In modo simile, è possibile utilizzare la funzione SOMMA.PIÙ.SE per sommare i valori in un dato intervallo di date. Ad esempio, la seguente formula SOMMA.PIÙ.SE somma i valori delle celle C2:C9 se la data in colonna B cade tra il 1-ott-2017 e il 31-ott-2017, inclusi.

=SOMMA.PIÙ.SE(C2:C9; B2:B9; ">=1/10/2017"; B2:B9; "<=31/10/2017")

Lo stesso risultato può essere ottenuto calcolando la differenza di due funzioni SOMMA.SE, come dimostrato in questo esempio: Come sommare valori che corrispondono ad un intervallo di date. Tuttavia, SOMMA.PIÙ.SE è molto più semplice e più comprensibile, non è vero?

Esempio 3. SOMMA.PIÙ.SE con celle vuote e non vuote

Quando si analizzano report e altri dati, è spesso necessario sommare valori corrispondenti a celle vuote oppure non vuote.

E adesso vediamo come sia possibile utilizzare una formula SOMMA.PIÙ.SE con criteri “vuote” e “non-vuote” sui dati reali.

Supponiamo che abbiate la data dell’ordine nella colonna B, la data di consegna nella colonna C e la Q.tà in colonna D. Come si trova il totale dei prodotti che non sono ancora stati consegnati?

Ossia, volete conoscere la somma dei valori corrispondenti alle celle non vuote nella colonna B e alle celle vuote nella colonna C. La soluzione è quella di utilizzare una formula SOMMA.PIÙ.SE con due criteri:

=SOMMA.PIÙ.SE(D2:D10; B2:B10;"<>"; C2:C10;"=")

Usare SOMMA.SE con più di un criterio O

Come indicato all’inizio di questo tutorial, la funzione SOMMA.PIÙ.SE è progettata con una logica E. Ma cosa succede se fosse necessario sommare valori con più criteri O, se, cioè, almeno una delle condizioni viene soddisfatta?

Esempio 1. SOMMA.SE + SOMMA.SE

La soluzione più semplice è quella di sommare i risultati restituiti da diverse funzioni SOMMA.SE. Ad esempio, la seguente formula mostra come trovare il totale dei prodotti consegnati da Michele e Giovanni:

=SOMMA.SE(C2:C9;"Michele"; D2:D9) + SOMMA.SE(C2:C9; "Giovanni"; D2:D9)

Come potete vedere, la prima funzione SOMMA.SE somma le quantità corrispondenti a “Michele”, l’altra funzione SOMMA.SE restituisce gli importi relativi a “Giovanni” e quindi si sommano questi 2 numeri.

Esempio 2. SOMMA e SOMMA.SE con un argomento in forma di matrice

La soluzione qui sopra è molto semplice e può darvi il risultato rapidamente quando ci sono solo un paio di criteri. Ma una formula SOMMA.SE + SOMMA.SE può crescere enormemente se si vogliono sommare valori con più condizioni O. In questo caso, un approccio migliore è quello di utilizzare un argomento criteri in forma di matrice nella funzione SOMMA.SE. Adesso esaminiamo questo approccio.

Potete iniziare elencando tutte le condizioni separate da un punto e racchiudendo l’elenco in {parentesi graffe}, questa viene tecnicamente definita una matrice.

Nell’esempio precedente, se volete sommare i prodotti consegnati da Giovanni, Michele e Luigi, i criteri di matrice appariranno come {“Giovanni”.”Michele”.”Luigi”}. E la funzione SOMMA.SE completa sarà =SOMMA.SE(C2:C9; {“Giovanni”.”Michele”.”Luigi”}; D2:D9).

L’argomento in forma di matrice che consiste di 3 valori obbliga la formula SOMMA.SE a restituire tre risultati distinti, ma poiché scriviamo la formula in una sola cella, restituirà solo il primo risultato, cioè il totale dei prodotti consegnati da Giovanni. Per far funzionare questo approccio di criteri in forma di matrice, dovete usare un piccolo trucco: racchiudere la formula SOMMA.SE in una funzione SOMMA, come segue:

=SOMMA(SOMMA.SE(C2:C9; {"Giovanni"."Michele"."Luigi"}; D2:D9))

Come potete vedere, un criterio in forma di matrice rende la formula molto più compatta rispetto a SOMMA.SE + SOMMA.SE e consente di aggiungere quanti valori volete nella matrice.

Questo approccio funziona con i numeri così come con i valori testuali. Ad esempio, se invece dei nomi dei fornitori in colonna C aveste avuto degli ID fornitore come 1, 2, 3 ecc., la vostra formula SOMMA.SE sarebbe simile a questa:

=SOMMA(SOMMA.SE(C2:C9; {1.2.3}; D2:D9))

A differenza dei valori testuali, i numeri non devono essere racchiusi in doppie virgolette negli argomenti in forma di matrice.

Esempio 3. MATR.SOMMA.PRODOTTO e SOMMA.SE

Nel caso in cui preferiate elencare i criteri in alcune celle invece di specificarli direttamente nella formula, è possibile utilizzare SOMMA.SE in combinazione con la funzione MATR.SOMMA.PRODOTTO che moltiplica i componenti nelle matrici specificate e restituisce la somma di tali moltiplicazioni.

=MATR.SOMMA.PRODOTTO(SOMMA.SE(C2:C9; G2:G4; D2:D9))

Dove G2:G4 sono le celle contenenti i criteri, i nomi dei fornitori nel nostro caso, come illustrato nella schermata qui sotto.

Ma naturalmente, non vi impedisce di elencare i valori in un criterio in forma di matrice della funzione SOMMA.SE se volete:

=MATR.SOMMA.PRODOTTO(SOMMA.SE(C2:C9; {"Michele"."Giovanni"."Luigi"}; D2:D9))

Il risultato restituito da entrambe le formule sarà identico a quello che si vede nello screenshot:

Usare SOMMA.PIÙ.SE con più di un criterio O

Se volete sommare condizionalmente i valori non semplicemente con diverse condizioni O, ma con diversi set di condizioni, è necessario utilizzare SOMMA.PIÙ.SE anziché SOMMA.SE. Le formule saranno molto simili a quelle che abbiamo appena discusso.

Come al solito, un esempio potrebbe aiutare a illustrare meglio il punto. Nella nostra tabella di fornitori di frutta, aggiungiamo la data di consegna (colonna E) e troviamo la quantità totale consegnata da Michele, Giovanni e Luigi in ottobre.

Esempio 1. SOMMA.PIÙ.SE + SOMMA.PIÙ.SE

La formula prodotta da questo approccio include molte ripetizioni e sembra “ingombrante”, ma è facile da capire e, soprattutto, funziona 🙂

=SOMMA.PIÙ.SE(D2:D9; C2:C9; "Michele"; E2:E9;">=1/10/2016"; E2:E9; "<=31/10/2016") +
SOMMA.PIÙ.SE(D2:D9; C2:C9; "Luigi"; E2:E9;">=1/10/2016"; E2:E9; "<=31/10/2016") +
SOMMA.PIÙ.SE(D2:D9; C2:C9; "Giovanni"; E2:E9;">=1/10/2016"; E2:E9; "<=31/10/2016")

Come potete vedere, si scrive una funzione SOMMA.PIÙ.SE separata per ciascuno dei fornitori, comprendente due condizioni: uguali o maggiore al 1/10 (“> = 1/10/2016”) e uguale o minore del 31 ottobre (“<= 31/10/2016”) e quindi si sommano i risultati.

Esempio 2. SOMMA e SOMMA.PIÙ.SE con un argomento in forma di matrice

Ho spiegato l’essenza di questo approccio nell’esempio con SOMMA.SE, quindi ora possiamo semplicemente copiare quella formula, modificare l’ordine degli argomenti (come ricorderete, è diverso tra SOMMA.SE e SOMMA.PIÙ.SE) e aggiungere gli ulteriori criteri. La formula risultante è più compatta di SOMMA.PIÙ.SE + SOMMA.PIÙ.SE:

=SOMMA(SOMMA.PIÙ.SE(D2:D9; C2:C9; {"Michele"."Giovanni"."Luigi"}; E2:E9; ">=1/10/2016"; E2:E9; "<=31/10/2016"))

Il risultato restituito da questa formula è esattamente lo stesso che vedete nella schermata precedente.

Esempio 3. MATR.SOMMA.PRODOTTO e SOMMA.PIÙ.SE

Come ricorderete, l’approccio con MATR.SOMMA.PRODOTTO differisce dai precedenti due per il fatto che i criteri possono venire inseriti ognuno in una cella separata piuttosto che specificarli direttamente nella formula. Nel caso di più set di criteri, la funzione MATR.SOMMA.PRODOTTO non sarà sufficiente e dovrete impiegare anche VAL.NUMERO e CONFRONTA.

Quindi, supponendo che i nomi dei fornitori siano nelle celle H1:H3, la data di inizio nella cella H4 e la data di fine nella cella H5, la nostra formula MATR.SOMMA.PRODOTTO sarà come questa:

=MATR.SOMMA.PRODOTTO(--(E2:E9>=H4); --(E2:E9<=H5); --(VAL.NUMERO(CONFRONTA(C2:C9; H1:H3; 0))); D2:D9)

Molte persone potrebbero chiedersi perché utilizzare il doppio meno (–) nelle formule MATR.SOMMA.PRODOTTO. Il punto è che MATR.SOMMA.PRODOTTO ignora tutti i valori non numerici, mentre gli operatori di confronto nella nostra formula restituiscono valori booleani (VERO / FALSO), non numerici. Per convertire questi valori booleani in 1 e 0, utilizziamo il doppio segno meno, che viene tecnicamente chiamato operatore doppio unario. Il primo unario trasforma i VERO / FALSO rispettivamente in -1/0. Il secondo unario nega i valori, vale a dire ne inverte il segno, trasformandoli in +1 e 0, che la funzione MATR.SOMMA.PRODOTTO può capire.

Spero che la spiegazione qua sopra abbia senso :). E anche se non l’avesse, basta ricordare questa regola: utilizzate l’operatore doppio unario (–) quando utilizzate gli operatori di confronto nelle formule MATR.SOMMA.PRODOTTO.

Usare SOMMA in formule matriciali

Come ricorderete, Microsoft ha implementato la funzione SOMMA.PIÙ.SE in Excel 2007. Se qualcuno utilizza ancora Excel 2003, 2000 o precedenti, è necessario utilizzare una formula matriciale con SOMMA per sommare valori con più criteri E. Naturalmente, questo approccio funziona anche nelle versioni moderne di Excel 2016 – 2007, e può essere considerato un antenato della funzione SOMMA.PIÙ.SE.

Nelle formule SOMMA.SE sopra descritte, avete già utilizzato argomenti in forma di matrice, ma una formula matriciale è qualcosa di diverso.

Esempio 1. Somma con più criteri E in Excel 2003 e precedenti

Torniamo al primo esempio in cui abbiamo calcolato la somma delle quantità relative a un dato frutto e fornitore:

Come sapete già, questo compito si può facilmente portare a termine usando una comune formula SOMMA.PIÙ.SE:

=SOMMA.PIÙ.SE(C2:C9; A2:A9; "Mele"; B2:B9; "Luigi")

E adesso vediamo come può essere completato lo stesso calcolo nelle versioni precedenti di “Excel”, quelle senza SOMMA.PIÙ.SE. Innanzitutto, annotate tutte le condizioni che dovrebbero essere soddisfatte sotto forma di intervallo = “condizione”. In questo esempio, abbiamo due coppie intervallo / condizione:

Condizione 1: A2:A9="Mele"

Condizione 2: B2:B9="Luigi"

Quindi, scrivete una formula SOMMA che “moltiplichi” tutte le coppie di condizioni intervallo / condizione, ognuna racchiusa tra parentesi. L’ultimo moltiplicatore è l’intervallo da sommare: C2:C9 nel nostro caso:

=SOMMA((A2:A9="Mele") * ( B2:B9="Luigi") * ( C2:C9))

Come mostrato nella schermata qui sotto, la formula funziona perfettamente nell’ultima versione di Excel 2016.

Esempio 2. Formule matriciali con SOMMA nelle versioni moderne di Excel

Anche nelle versioni moderne di Excel 2016, 2013, 2010 o 2007, la potenza della funzione SOMMA non dovrebbe essere sottovalutata. La formula matriciale con SOMMA non è semplicemente una ginnastica per la mente, ma ha un valore pratico, come dimostrato nell’esempio seguente.

Supponiamo di avere due colonne B e C e dovete contare quante volte la colonna C è maggiore della colonna B quando un valore nella colonna C è maggiore o uguale a 10. Una soluzione immediata che viene in mente è usare una formula matriciale con SOMMA:

=SOMMA((C1:C10>=10) * (C1:C10>B1:B10))

Non vedete un’applicazione pratica per la formula precedente? guardatela sotto un altro punto di vista 🙂

Supponiamo che abbiate un elenco di ordini, come mostrato nella schermata qui sotto e che volete sapere quanti prodotti non sono stati consegnati completamente entro una determinata data. Tradotto in lingua di Excel, abbiamo le seguenti condizioni:

Condizione 1: il valore nella colonna B (articoli ordinati) è maggiore di 0

Condizione 2: il valore nella colonna C (consegnato) è inferiore a colonna B 

Condizione 3: la data in colonna D (data di scadenza) è inferiore a 1/11/2016

Mettendo insieme le tre coppie intervallo / condizione, si ottiene la seguente formula:

=SOMMA((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))

Bene, gli esempi di formula descritti in questo tutorial hanno appena scalfito la superficie di ciò che le funzioni di Excel SOMMA.PIÙ.SE e SOMMA.SE possono realmente fare. Ma speriamo che abbiano aiutato ad indirizzarvi nella giusta direzione e che ora possiate sommare valori nelle cartelle di lavoro di Excel, non importa quante condizioni intricate dobbiate considerare.