Questo tutorial spiega la differenza tra le funzioni SOMMA.SE e SOMMA.SE in termini di sintassi e e fornisce una serie di esempi di formule per sommare i valori con più criteri AND / OR in Excel 2019, 2016, 2013, 2010, 2007, 2003 e versioni precedenti.
Come tutti sanno, Microsoft Excel fornisce un array di funzioni per eseguire vari calcoli con i dati. Alcuni articoli fa, abbiamo esplorato COUNTIF e COUNTIFS, progettati per contare le celle in base a una singola condizione e a più condizioni, rispettivamente. La scorsa settimana abbiamo trattato Excel SUMIF che aggiunge valori che soddisfano i criteri specificati. Ora è il momento di esaminare la versione plurale di SUMIF – Excel SUMIFS che consente di sommare i valori in base a più criteri.
Coloro che hanno familiarità con la funzione SUMIF potrebbero pensare che la conversione in SUMIFS richieda solo un extra “S” e alcuni criteri aggiuntivi. Questo sembrerebbe abbastanza logico … ma “logico” non è sempre il caso quando si ha a che fare con Microsoft:)
- SUMIF function – syntax & utilizzo
- Funzione SUMIFS – sintassi & utilizzo
- Come utilizzare SUMIFS in Excel (logica AND)
- SUMIF Excel con più criteri (logica OR)
- SUMIF Excel con più condizioni OR
- Utilizzo di SOMMA Excel in formule di matrice
Funzione SOMMA.SE di Excel – sintassi & utilizzo
La funzione SOMMA.SE viene utilizzata per sommare in modo condizionale i valori in base a un singolo criterio. Abbiamo discusso la sintassi SOMMA.SE in dettaglio nellarticolo precedente, quindi lascia che ti dia solo un breve riepilogo ora.
-
range– lintervallo di celle da valutare in base ai tuoi criteri, obbligatorio. -
criteria– la condizione che deve essere soddisfatta, obbligatorio. -
sum_range: le celle da sommare se la condizione è soddisfatta, facoltativo.
Come vedi, il la sintassi della funzione SOMMA.SE di Excel consente solo una condizione. E ancora, diciamo che Excel SUMIF può essere utilizzato per sommare i valori con più criteri. Come può essere? Aggiungendo i risultati di diverse funzioni SOMMA.SE e utilizzando formule SOMMA.SE con criteri di matrice, come dimostrato negli esempi seguenti.
Funzione SOMMA.SE di Excel – sintassi &
Si utilizza SUMIFS in Excel per trovare una somma condizionale di valori basata su più criteri. La funzione SUMIFS è stata introdotta in Excel 2007, quindi puoi utilizzarla in tutte le versioni moderne di Excel 2019, 2016, 2013, 2010 e 2007.
Rispetto a SUMIF, la sintassi SUMIFS è un po più complessa :
I primi 3 argomenti sono obbligatori, gli intervalli aggiuntivi ei criteri associati sono facoltativi.
-
sum_range– una o più celle da sommare, obbligatorie. Può essere una singola cella, un intervallo di celle o un intervallo denominato. Vengono sommate solo le celle con numeri; i valori vuoto e di testo vengono ignorati. -
criteria_range1: il primo intervallo da valutare in base ai criteri associati, obbligatorio. -
criteria1– la prima condizione che deve essere soddisfatta, obbligatoria. È possibile fornire i criteri sotto forma di numero, espressione logica, riferimento di cella, testo o unaltra funzione di Excel. Ad esempio, puoi utilizzare criteri come 10, “> = 10”, A1, “ciliegie” o TODAY (). -
criteria_range2, criteria2, …: si tratta di intervalli e criteri aggiuntivi ad essi associati, facoltativo. È possibile utilizzare fino a 127 coppie di intervalli / criteri nelle formule SUMIFS.
E ora, lascia ” s dai unocchiata alla formula SUMIFS di Excel con due condizioni. Supponi di avere una tabella che elenca le partite di frutta da diversi fornitori. Hai i nomi dei frutti nella colonna A, i nomi dei fornitori nella colonna B e la quantità nella colonna C. Quello che vuoi è trovare una somma degli importi relativi a un dato frutto e fornitore, es tutte le mele fornite da Pete.
Quando “stai imparando qualcosa di nuovo” è sempre una buona idea iniziare con cose semplici. Quindi, per cominciare, definiamo tutti gli argomenti per la nostra formula SUMIFS:
- intervallo_somma – C2: C9
- intervallo_criteri1 – A2: A9
- criteri1 – “mele”
- intervallo_criteri2 – B2: B9
- criteri2 – “Pete”
Ora assembla i parametri precedenti e tu otterrà la seguente formula SUMIFS:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Per perfezionare la formula inoltre, è possibile sostituire i criteri di testo “mele” e “Pete” con riferimenti di cella.In questo caso, non sarà necessario modificare la formula per calcolare la quantità di altra frutta di un altro fornitore:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Utilizzo di SUMIFS e SUMIF in Excel: cose da ricordare
Poiché lo scopo di questo tutorial è quello di coprire tutti i modi possibili per sommare i valori in base a diverse condizioni, discuteremo esempi di formule con entrambe le funzioni: Excel SUMIFS e SUMIF con più criteri. Per usarli correttamente, è necessario capire chiaramente cosa hanno in comune queste due funzioni e in che modo sono diverse.
Mentre la parte comune è chiara – destinazione e parametri simili – le differenze non sono così ovvie, anche se molto essenziali.
1. Lordine degli argomenti
Nelle funzioni SUMIF e SUMIFS di Excel, lordine degli argomenti è diverso. In particolare, sum_range è il primo parametro in SUMIFS, ma è terzo in SUMIF.
A prima vista, può sembrare che Microsoft complichi deliberatamente la curva di apprendimento per i suoi utenti. Howev ehm, a uno sguardo più attento, vedrai il ragionamento che cè dietro. Il punto è che intervallo_somma è opzionale in SUMIF. Se lo ometti, nessun problema, la tua formula SUMIF sommerà i valori nellintervallo (primo parametro).
In SUMIFS, sum_range è molto importante e obbligatorio, ed è per questo che viene prima. Probabilmente i ragazzi di Microsoft hanno pensato che dopo aver aggiunto la decima o la centesima coppia intervallo / criterio, qualcuno potrebbe dimenticare di specificare lintervallo da sommare:)
2. Lintervallo della somma e lintervallo dei criteri devono essere di dimensioni uguali
Nella funzione SUMIF, largomento intervallo_somma non deve necessariamente essere della stessa dimensione dellargomento intervallo, purché la cella in alto a sinistra sia a destra. In Excel SUMIFS, ogni intervallo_criteri deve contenere lo stesso numero di righe e colonne del parametro intervallo_somma.
Ad esempio, =SUMIF(A2:A9,F1,C2:C18) restituirà il risultato corretto perché Excel considera solo la cella più in alto a sinistra nellargomento sum_range (C2 in questo esempio, che è corretto), e quindi include tante colonne e righe quante sono contenute nellargomento range.
Va bene, strategia sufficiente (cioè teoria) , entriamo nelle tattiche (es. esempi di formule 🙂
Come usare SUMIFS in Excel – esempi di formule
Un momento fa, abbiamo discusso una semplice formula SUMIFS con due testi criteri. Allo stesso modo, puoi utilizzare Excel SUMIFS con più criteri espressi da numeri, date, espressioni logiche e altre funzioni di Excel.
Esempio 1. Excel SUMIFS con operatori di confronto
Nella nostra tabella dei fornitori di frutta, supponi di voler sommare tutte le consegne di Mike con una quantità pari o superiore a 200. Per fare ciò, utilizzi loperatore di confronto “maggiore di o r uguale a “(> =) nei criteri2 e ottieni la seguente formula SUMIFS:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Abbiamo trattato in dettaglio tutti i possibili operatori di confronto quando abbiamo discusso della funzione SUMIF di Excel, gli stessi operatori può essere utilizzato nei criteri SUMIFS. Ad esempio, la seguente formula restituisce la somma di tutti i valori nelle celle C2: C9 maggiori o uguali a 200 e minori o uguali a 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Esempio 2. Utilizzo di SUMIFS di Excel con le date
Nel caso in cui desideri sommare i valori con più criteri in base alla data corrente, utilizza la funzione TODAY () nel tuo SUMIFS criteri, come dimostrato di seguito. La seguente formula somma i valori nella colonna D se una data corrispondente nella colonna C rientra negli ultimi 7 giorni, compreso oggi:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
In modo simile, puoi utilizzare la funzione SOMMA.SE di Excel per sommare i valori in un determinato intervallo di date. Ad esempio, la seguente formula SUMIFS aggiunge i valori nelle celle C2: C9 se una data nella colonna B è compresa tra l1 ottobre 2014 e il 31 ottobre 2014 inclusi.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Lo stesso risultato può essere ottenuto calcolando la differenza di due funzioni SOMMA.SE, come dimostrato in questo esempio – Come utilizzare SOMMA.SE per sommare i valori in un determinato intervallo di date. Tuttavia, Excel SUMIFS è molto più semplice e comprensibile, non è vero?
Esempio 3. Excel SUMIFS con celle vuote e non vuote
Quando si analizzano report e altri dati, si potrebbe spesso essere necessario sommare i valori corrispondenti a celle vuote o non vuote.
E ora, vediamo come puoi utilizzare una formula SUMIFS con criteri “vuoti” e “non vuoti” su valori reali dati.
Supponi di avere una data dellordine nella colonna B, la data di consegna nella colonna C e Qtà. nella colonna D. Come trovi il totale dei prodotti che non sono stati ancora consegnati? Cioè, vuoi conoscere la somma dei valori corrispondenti alle celle non vuote nella colonna B e alle celle vuote nella colonna C.
La soluzione è utilizzare la formula SUMIFS con 2 criteri:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Utilizzo di Excel SUMIF con più criteri OR
Come notato allinizio di questo tutorial, la funzione SUMIFS è progettata con logica AND. Ma cosa succede se devi sommare i valori con più criteri OR, cioè quando almeno una delle condizioni è soddisfatta?
Esempio 1. SOMMA.SE + SOMMA.SE
La soluzione più semplice è sommare i risultati restituiti da diverse funzioni SUMIF. Ad esempio, la formula seguente mostra come trovare il totale dei prodotti consegnati da Mike e John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Come vedi, la prima funzione SUMIF aggiunge le quantità corrispondenti a “Mike”, laltra funzione SUMIF restituisce gli importi relativi a “John” e poi aggiungi questi 2 numeri.
Esempio 2. SUM & SUMIF con un argomento array
La soluzione sopra è molto semplice e può portare a termine il lavoro rapidamente quando ci sono solo un paio di criteri. Ma una formula SOMMA.SE + SOMMA.SE può crescere enormemente se si desidera sommare valori con più condizioni OR. In questo caso, un approccio migliore consiste nellusare un argomento dei criteri di matrice nella funzione SUMIF. Esaminiamo ora questo approccio.
Puoi iniziare elencando tutte le tue condizioni separate da virgole e poi racchiudere lelenco separato da virgole risultante tra {parentesi graffe}, che tecnicamente è chiamato array.
Largomento della matrice composto da 3 valori forza la formula SUMIF a restituire tre risultati separati, ma poiché scriviamo la formula in una singola cella, restituirà solo il primo risultato, ovvero il totale dei prodotti forniti da John Per far funzionare questo approccio basato sui criteri degli array, devi usare un altro piccolo trucco: racchiudi la tua formula SOMMA.SE in una funzione SOMMA, come questa:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Come puoi vedere, un criterio di matrice rende la formula molto più compatta rispetto a SUMIF + SUMIF e ti consente di aggiungere tanti valori quanti ti piace nellarray.
Questo approccio funziona sia con i numeri che con i valori di testo. Ad esempio, se invece dei nomi dei fornitori nella colonna C, avessi ID fornitore come 1, 2, 3 ecc. ., allora la tua formula SUMIF sarebbe simile a questa:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
A differenza dei valori di testo, i numeri non devono “essere racchiusi tra virgolette doppie argomenti dellarray.
Esempio 3. SUMPRODUCT & SUMIF
In ogni caso, il tuo modo preferito è elencare i criteri in alcune celle piuttosto che specificali direttamente nella formula, puoi utilizzare SUMIF insieme alla funzione SUMPRODUCT che moltiplica i componenti negli array dati e restituisce la somma di tali prodotti.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Dove G2: G4 sono le celle contenenti i tuoi criteri, i nomi dei fornitori nel nostro caso, come illustrato nello screenshot qui sotto.
Ma ovviamente nulla ti impedisce di elencare i valori in un criterio di matrice della funzione SOMMA.SE se si desidera:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Il risultato restituito da entrambe le formule sarà identico a quello che vedi nello screenshot:
Excel SUMIFS con criteri OR multipli
Se si desidera sommare in modo condizionale i valori in Excel non semplicemente con più condizioni OR, ma con diversi set di condizioni, sarà necessario utilizzare SUMIFS invece di SUMIF. 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 dei fornitori di frutta, aggiungiamo la Data di consegna (colonna E) e trova la quantità totale consegnata da Mike, John e Pete in ottobre.
Esempio 1. SUMIFS + SUMIFS
La formula prodotta da questo approccio include molte ripetizioni e sguardi ingombrante, ma è facile da capire e, soprattutto, funziona 🙂
Esempio 2. SUM & SUMIFS con un argomento di matrice
Ho provato a spiegare lessenza di questo approccio nellesempio SOMMA.SE, quindi ora possiamo semplicemente copiare quella formula, cambiare lordine degli argomenti (come ricordi, è diverso in SOMMA.SE e SOMMA.SE) e aggiungere criteri aggiuntivi. la formula risultante è più compatta di SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Il risultato restituito da questa formula è esattamente lo stesso che vedi in lo screenshot sopra.
Exampl e 3. SUMPRODUCT & SUMIFS
Come ricordi, lapproccio SUMPRODUCT differisce dai due precedenti per il modo in cui inserisci ciascuno dei criteri in una cella separata piuttosto che specificarli direttamente nella formula. In caso di più set di criteri, la funzione SUMPRODUCT non sarà sufficiente e dovrai utilizzare anche ISNUMBER e MATCH.
Quindi, supponendo che i nomi dei materiali di consumo siano nelle celle H1: H3, la data di inizio è nella cella H4 e la data di fine nella cella H5, la nostra formula SUMPRODUCT assume la seguente forma:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Molte persone si chiedono perché utilizzare il doppio trattino (-) nelle formule SUMPRODUCT. Il punto è che Excel SUMPRODUCT ignora tutti i valori tranne quelli numerici, mentre gli operatori di confronto nella nostra formula restituiscono valori booleani (VERO / FALSO), che non sono numerici. Per convertire questi valori booleani in 1 “se 0” s, si utilizza il doppio segno meno, che tecnicamente è chiamato doppio operatore unario. Il primo unario forza TRUE / FALSE a -1/0, rispettivamente. Il secondo unario nega i valori, cioè inverte il segno, trasformandoli in +1 e 0, che la funzione SUMPRODUCT può capire.
Spero che la spiegazione sopra abbia senso. E anche se non lo fa, ricorda solo questa regola pratica: usa il doppio operatore unario (-) quando utilizzi operatori di confronto nelle formule SUMPRODUCT.
Uso di Excel SUM nelle formule di matrice
Come ricordi, Microsoft ha implementato la funzione SOMMA.SE in Excel 2007. Se qualcuno utilizza ancora Excel 2003, 2000 o versioni precedenti, dovrai utilizzare una formula di matrice SOMMA per aggiungere valori con più criteri AND. Naturalmente, questo approccio funziona anche nelle versioni moderne di Excel 2013-2007 e può essere considerata una controparte antiquata della funzione SOMMA.SE.
Nelle formule SOMMA.SE discusse sopra, hai già utilizzato argomenti di matrice, ma una formula di matrice è qualcosa di diverso.
Esempio 1. Somma con più criteri AND in Excel 2003 e versioni precedenti
Torniamo al primo esempio in cui abbiamo scoperto una somma di importi relativi a un determinato frutto e fornitore:
Come già saprai, questa attività può essere facilmente eseguita utilizzando un formula SUMIFS ordinaria:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
E ora, vediamo come lo stesso compito può essere adempiuto nelle prime versioni “SUMIFS-free” di Excel . Prima di tutto, scrivi tutte le condizioni che dovrebbero essere soddisfatte sotto forma di range = “condition”. In questo esempio, abbiamo due coppie intervallo / condizione:
Condizione 1: A2: A9 = “mele”
Condizione 2: B2: B9 = “Pete”
Quindi, scrivi una formula SOMMA che “moltiplica” tutte le tue coppie intervallo / condizione, ciascuna racchiusa tra parentesi. Lultimo moltiplicatore è lintervallo della somma, C2: C9 nel nostro caso:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Come illustrato nello screenshot qui sotto, la formula funziona perfettamente nellultima versione di Excel 2013.
Esempio 2. Formule di matrice SUM nelle versioni moderne di Excel
Anche nelle versioni moderne di Excel, la potenza della funzione SOMMA non deve essere sottovalutata. La formula di matrice SOMMA non è semplicemente ginnastica della mente, ma ha un valore pratico, come dimostrato nellesempio seguente.
Supponi , hai due colonne, B e C, e devi 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 la SOMMA formula di matrice:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Non vedere niente di pratico applicazione alla formula di cui sopra? Pensaci in un altro modo:)
Supponi di avere la lista degli ordini come mostrato nello screenshot qui sotto e di voler sapere quanti prodotti non sono stati consegnati per intero entro una determinata data. Tradotto nella lingua di Excel, abbiamo le seguenti condizioni:
Condizione 1: un valore nella colonna B (articoli ordinati) è maggiore di 0
Condizione 2: un valore nella colonna C (Consegnato) in meno che nella colonna B
Condizione 3: una data nella colonna D (Data di scadenza) è inferiore a 01/11/2014.
Inserendo i tre intervalli / coppie di condizioni insieme, ottieni la seguente formula:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Bene, gli esempi di formule discussi in questo tutorial hanno solo scalfito la superficie di ciò che le funzioni SOMMA.SE e SOMMA.SE di Excel possono davvero fare. Ma si spera che ti abbiano aiutato a indirizzarti nella giusta direzione e ora puoi sommare i valori nelle cartelle di lavoro di Excel, non importa quante complicate condizioni devi considerare.
- Come utilizzare SUMIF in Excel – esempi di formule per sommare in modo condizionale le celle
- Come utilizzare SUMIFS e SUMIF di Excel con più criteri – esempi di formule
- Come utilizzare CERCA.VERT & SOMMA o SOMMA.SE in Excel
- SOMMA.SE in Fogli Google con esempi di formule
- CONTA.SE in Excel – conta se no vuoto, maggiore di, duplicato o univoco
- Come contare e sommare le celle per colore in Excel