Questo tutorial fornisce una dimostrazione dettagliata di come utilizzare le funzioni SOMMA.SE e SOMMA.SE in Fogli Google con formule ed esempi.
SOMMA.SE è una delle funzioni matematiche in Fogli Google, utilizzata per sommare le celle in modo condizionale. Fondamentalmente, la funzione SOMMA.SE cerca una condizione specifica in un intervallo di celle e quindi somma i valori che soddisfano la condizione data.
Ad esempio, hai un elenco di spese nei fogli di Google e vuoi solo sommare le spese che superano un certo valore massimo. Oppure si dispone di un elenco di articoli dell'ordine e dei relativi importi e si desidera conoscere solo l'importo totale dell'ordine di un articolo specifico. È qui che torna utile la funzione SOMMA.SE.
Il SOMMA.SE può essere utilizzato per sommare valori in base a condizione numerica, condizione testo, condizione data, caratteri jolly nonché in base a celle vuote e non vuote. Fogli Google ha due funzioni per riassumere i valori in base a criteri: SOMMA.SE e SOMMA.SE. La funzione SOMMA.SE somma i numeri in base a una condizione mentre SOMMA.SE somma i numeri in base a più condizioni.
In questo tutorial spiegheremo come utilizzare le funzioni SOMMA.SE e SOMMA.SE in Fogli Google per sommare i numeri che soddisfano determinate condizioni.
Funzione SOMMA.SE in Fogli Google - Sintassi e argomenti
La funzione SOMMA.SE è solo una combinazione della funzione SOMMA e SE. La funzione SE esegue la scansione dell'intervallo di celle per una determinata condizione, quindi la funzione SOMMA somma i numeri corrispondenti alle celle che soddisfano la condizione.
Sintassi della funzione SOMMA.SE:
La sintassi della funzione SOMMA.SE in Fogli Google è la seguente:
=SOMMA.SE(intervallo, criteri, [intervallo_somma])argomenti:
gamma - L'intervallo di celle in cui cerchiamo le celle che soddisfano i criteri.
criteri – I criteri che determinano quali celle devono essere aggiunte. È possibile basare il criterio su numero, stringa di testo, data, riferimento di cella, espressione, operatore logico, carattere jolly e altre funzioni.
somma_intervallo – Questo argomento è facoltativo. È l'intervallo di dati con i valori da sommare se la voce dell'intervallo corrispondente corrisponde alla condizione. Se non includi questo argomento, viene invece sommato l'"intervallo".
Vediamo ora come utilizzare la funzione SOMMA.SE per sommare valori con criterio diverso.
Funzione SOMMA.SE con criteri numerici
È possibile sommare i numeri che soddisfano determinati criteri in un intervallo di celle utilizzando uno dei seguenti operatori di confronto per creare criteri.
- maggiore di (>)
- minore di (<)
- maggiore o uguale a (>=)
- minore o uguale a (<=)
- uguale a (=)
- non uguale a ()
Supponiamo che tu abbia il seguente foglio di calcolo e che tu sia interessato alle vendite totali che sono 1000 o superiori.
Ecco come puoi inserire la funzione SOMMA.SE:
Innanzitutto, seleziona la cella in cui desideri visualizzare l'output della somma (D3). Per sommare i numeri in B2:B12 che sono maggiori o uguali a 1000, digita questa formula e premi "Invio":
=SOMMA.SE(B2:B12,">=1000",B2:B12)In questa formula di esempio, gli argomenti intervallo e intervallo_somma (B2:B12) sono gli stessi, perché i numeri e i criteri di vendita vengono applicati allo stesso intervallo. E abbiamo inserito il numero prima dell'operatore di confronto e lo abbiamo racchiuso tra virgolette perché i criteri dovrebbero essere sempre racchiusi tra virgolette doppie, tranne che per un riferimento di cella.
La formula ha cercato numeri maggiori o uguali a 1000, quindi ha sommato tutti i valori corrispondenti e ha mostrato il risultato nella cella D3.
Poiché gli argomenti range e sum_range sono gli stessi, puoi ottenere lo stesso risultato senza gli argomenti sum_range nella formula, in questo modo:
=SOMMA.SE(B2:B12;">=1000")
Oppure puoi fornire il riferimento di cella (D2) che contiene il numero anziché i criteri numerici e unire l'operatore di confronto con quel riferimento di cella nell'argomento criteri:
=SOMMA.SE(B2:B12,">="&D2)Come puoi vedere, l'operatore di confronto è ancora inserito tra virgolette doppie e l'operatore e il riferimento di cella sono concatenati da una e commerciale (&). E non è necessario racchiudere il riferimento di cella tra virgolette.
Nota: Quando fai riferimento alla cella che contiene i criteri, assicurati di non lasciare spazi iniziali o finali nel valore della cella. Se il tuo valore ha uno spazio non necessario prima o dopo il valore nella cella di riferimento, la formula restituirà "0" come risultato.
È inoltre possibile utilizzare altri operatori logici allo stesso modo per creare condizioni nell'argomento criteri. Ad esempio, per sommare valori inferiori a 500:
=SOMMA.SE(B2:B12;"<500")
Somma se i numeri sono uguali a
Se vuoi aggiungere numeri uguali a un certo numero, puoi inserire solo il numero o inserire il numero con il segno di uguale nell'argomento del criterio.
Ad esempio, per sommare gli importi di vendita corrispondenti (colonna B) per quantità (colonna C) i cui valori sono pari a 20, provare una di queste formule:
=SOMMA.SE(C2:C12,"=20",B2:B12)
=SOMMA.SE(C2:C12,"20",B2:B12)
=SOMMA.SE(C2:C12;E2,B2:B12)
Per sommare i numeri nella colonna B con una quantità non uguale a 20 nella colonna C, prova questa formula:
=SOMMA.SE(C2:C12,"20",B2:B12)
Funzione SOMMA.SE con criteri di testo
Se vuoi sommare i numeri in un intervallo di celle (colonna o riga) corrispondente alle celle che hanno un testo specifico, puoi semplicemente includere quel testo o la cella che contiene il testo nell'argomento criteri della tua formula SOMMA.SE. Si prega di notare che la stringa di testo deve essere sempre racchiusa tra virgolette (" ").
Ad esempio, se desideri l'importo totale delle vendite nella regione "Ovest", puoi utilizzare la formula seguente:
=SOMMA.SE(C2:C13,"Ovest",B2:B13)In questa formula, la funzione SOMMA.SE cerca il valore "Ovest" nell'intervallo di celle C2: C13 e somma il valore di vendita corrispondente nella colonna B. Quindi visualizza il risultato nella cella E3.
Puoi anche fare riferimento alla cella che contiene testo invece di utilizzare il testo nell'argomento criteri:
=SOMMA.SE(C2:C12;E2,B2:B12)
Ora, otteniamo le entrate totali di tutte le regioni eccetto "Ovest". Per fare ciò, useremo non uguale all'operatore () nella formula:
=SOMMA.SE(C2:C12,""&E2,B2:B12)
SOMMA.SE con caratteri jolly
Nel metodo sopra, la funzione SOMMA.SE con criteri di testo controlla l'intervallo rispetto al testo esatto specificato. Quindi somma i numeri parallelamente al testo esatto e ignora tutti gli altri numeri, inclusa la stringa di testo parzialmente abbinata. Per sommare i numeri con stringhe di testo a corrispondenza parziale, devi adattare uno dei seguenti caratteri jolly nei tuoi criteri:
?(punto interrogativo) viene utilizzato per trovare una corrispondenza con qualsiasi singolo carattere, in qualsiasi punto della stringa di testo.*(asterisco) viene utilizzato per trovare le parole corrispondenti insieme a qualsiasi sequenza di caratteri.~(tilde) viene utilizzato per abbinare i testi con un punto interrogativo (?) o un carattere asterisco (*).
Questo foglio di calcolo di esempio per i prodotti e le loro quantità sommerà i numeri con i caratteri jolly:
Asterisco (*) Carattere jolly
Ad esempio, se vuoi sommare le quantità di tutti i prodotti Apple, usa questa formula:
=SOMMA.SE(A2:A14;"Mela*", B2:B14)Questa formula SOMMA.SE trova tutti i prodotti con la parola "Mela" all'inizio e qualsiasi numero di caratteri dopo di essa (indicati da '*'). Una volta trovata la corrispondenza, riassume il Quantità numeri corrispondenti alle stringhe di testo corrispondenti.
È anche possibile utilizzare più caratteri jolly nei criteri. E puoi anche inserire caratteri jolly con riferimenti di cella anziché testo diretto.
Per fare ciò, i caratteri jolly devono essere racchiusi tra virgolette doppie ("") e concatenati con i riferimenti di cella:
=SOMMA.SE(A2:A14,"*"&D2&"*",B2:B14)Questa formula somma le quantità di tutti i prodotti che contengono la parola "Redmi", indipendentemente da dove si trova la parola nella stringa.
Punto interrogativo (?) Carattere jolly
È possibile utilizzare il carattere jolly punto interrogativo (?) per abbinare le stringhe di testo a qualsiasi singolo carattere.
Ad esempio, se vuoi trovare le quantità di tutte le varianti di Xiaomi Redmi 9, puoi utilizzare questa formula:
=SOMMA.SE(A2:A14,"Xiaomi Redmi 9?",B2:B14)La formula sopra cerca stringhe di testo con la parola "Xiaomi Redmi 9" seguita da singoli caratteri e somma i corrispondenti Quantità numeri.
Tilde (~) Carattere jolly
Se si desidera far corrispondere un vero punto interrogativo (?) o un carattere asterisco (*), inserire il carattere tilde (~) prima del carattere jolly nella parte condizione della formula.
Per sommare le quantità nella colonna B con la stringa corrispondente che ha un asterisco alla fine, inserisci la seguente formula:
=SOMMA.SE(A2:A14;"Samsung Galaxy V~*", B2:B14)
Per aggiungere quantità nella colonna B che hanno un punto interrogativo (?) nella colonna A nella stessa riga, prova la formula seguente:
=SOMMA.SE(A2:A14,"~?",B2:B14)
Funzione SOMMA.SE con criteri di data
La funzione SOMMA.SE può anche aiutarti a sommare in modo condizionale i valori in base a criteri di data, ad esempio i numeri corrispondenti a una determinata data, o prima di una data o dopo una data. È inoltre possibile utilizzare uno qualsiasi degli operatori di confronto con un valore di data per creare criteri di data per la somma dei numeri.
La data deve essere immessa nel formato di data supportato dai fogli di Google, o come riferimento di cella che contiene una data, o utilizzando una funzione di data come DATE() o TODAY().
Useremo questo foglio di calcolo di esempio per mostrarti come funziona la funzione SOMMA.SE con criteri di data:
Supponiamo di voler sommare gli importi delle vendite avvenuti il o prima (<=) il 29 novembre 2019 nel set di dati sopra, è possibile aggiungere quei numeri di vendita utilizzando la funzione SOMMA.SE in uno di questi modi:
=SOMMA.SE(C2:C13;"<=29 novembre 2019", B2:B13)La formula sopra controlla ogni cella da C2 a C13 e corrisponde solo a quelle celle che contengono date entro il 29 novembre 2019 (29/11/2019). Quindi somma l'importo delle vendite corrispondente a quelle celle corrispondenti dell'intervallo di celle B2: B13 e visualizza il risultato nelle celle E3.
La data può essere fornita alla formula in qualsiasi formato riconosciuto da Fogli Google, come "29 novembre 2019′, "29 novembre 2019" o "29/11/2019", ecc. Ricordare il valore della data e l'operatore deve essere sempre racchiuso tra virgolette doppie.
Puoi anche usare la funzione DATE() nei criteri invece che direct date vale:
=SOMMA.SE(C2:C13;"<="&DATA(2019,11,29);B2:B13)
In alternativa, puoi utilizzare il riferimento di cella anziché la data nella parte dei criteri della formula:
=SOMMA.SE(C2:C13,"<="&E2,B2:B13)
Se desideri sommare gli importi delle vendite in base alla data odierna, puoi utilizzare la funzione TODAY() nell'argomento criteri.
Ad esempio, per sommare tutti gli importi delle vendite per la data odierna, utilizzare questa formula:
=SOMMA.SE(C2:C13,OGGI(),B2:B13)
Funzione SOMMA.SE con celle vuote o non vuote
A volte, potrebbe essere necessario sommare i numeri in un intervallo di celle con celle vuote o non vuote nella stessa riga. In questi casi, puoi utilizzare la funzione SOMMA.SE per sommare i valori in base a criteri in cui le celle sono vuote o meno.
Somma se vuoto
Ci sono due criteri in Fogli Google per trovare celle vuote: "" o "=".
Ad esempio, se si desidera sommare tutto l'importo delle vendite che contengono stringhe di lunghezza zero (appare visivamente vuoto) nella colonna C, utilizzare le virgolette senza spazi nella formula:
=SOMMA.SE(C2:C13;"",B2:B13)
Per sommare tutto l'importo delle vendite nella colonna B con celle vuote complete nella colonna C, includi "=" come criterio:
=SOMMA.SE(C2:C13;"=";B2:B13)
Somma se non vuoto:
Se vuoi sommare le celle che contengono qualsiasi valore (non vuoto), puoi usare "" come criterio nella formula:
Ad esempio, per ottenere l'importo totale delle vendite con qualsiasi data, utilizzare questa formula:
=SOMMA.SE(C2:C13;"",B2:B13)
SOMMA.SE basato su criteri multipli con logica OR
Come abbiamo visto finora, la funzione SOMMA.SE è progettata per sommare numeri in base a un solo criterio, ma è possibile sommare valori in base a più criteri con la funzione SOMMA.SE in Fogli Google. Può essere fatto unendo più di una funzione SOMMA.SE in un'unica formula con logica OR.
Ad esempio, se si desidera sommare l'importo delle vendite nella regione "Ovest" o nella regione "Sud" (logica OR) nell'intervallo specificato (B2: B13), utilizzare questa formula:
=SOMMA.SE(C2:C13,"Ovest", B2:B13)+SOMMA.SE(C2:C13,"Sud", B2:B13)Questa formula somma le celle quando almeno una delle condizioni è TRUE. Quindi è noto come "logica OR". Somma anche i valori quando tutte le condizioni sono soddisfatte.
La prima parte della formula controlla l'intervallo C2:C13 per il testo "Ovest" e somma i valori nell'intervallo B2:B13 quando viene soddisfatta la corrispondenza. La seconda parte dei controlli per il valore del testo "Sud" nello stesso intervallo C2: C13 e quindi somma i valori con il testo corrispondente nello stesso intervallo_somma B2: B13. Quindi entrambe le somme vengono sommate e visualizzate nella cella E3.
Nei casi in cui viene soddisfatto un solo criterio, restituirà solo quel valore di somma.
Puoi anche utilizzare più criteri anziché solo uno o due. E se stai usando più criteri, è meglio usare un riferimento di cella come criterio invece di scrivere il valore diretto nella formula.
=SOMMA.SE(DO2:DO13, MI2, SI2: SI13)+SOMMA.SE(DO2:DO13, MI3, SI2: SI13)+SOMMA.SE(DO2:DO13, MI4, SI2: SI13)
SOMMA.SE con logica OR aggiunge valori quando viene soddisfatto almeno uno dei criteri specificati, ma se si desidera sommare i valori solo quando tutte le condizioni specificate sono soddisfatte, è necessario utilizzare la nuova funzione di pari livello SUMIFS().
Funzione SUMIFS in Fogli Google (criteri multipli)
Quando utilizzi la funzione SOMMA.SE per sommare valori in base a più criteri, la formula potrebbe diventare troppo lunga e complicata e potresti commettere errori. Inoltre SOMMA.SE ti consentirà di sommare i valori solo su un singolo intervallo e quando una delle condizioni è VERA. È qui che entra in gioco la funzione SUMIFS.
La funzione SUMIFS consente di sommare i valori in base a più criteri di corrispondenza in uno o più intervalli. E funziona con la logica AND, il che significa che può sommare valori solo quando tutte le condizioni fornite sono soddisfatte. Anche se una condizione è falsa, restituirà "0" come risultato.
Sintassi e argomenti della funzione SUMIFS
La sintassi della funzione SOMMA.PI.SE è la seguente:
=SUMIFS(intervallo_somma, intervallo_criteri1, criterio1, [intervallo_criteri2, ...], [criterio2, ...])Dove,
- somma_intervallo – L'intervallo di celle contenente i valori che desideri sommare quando tutte le condizioni sono soddisfatte.
- intervallo_criteri1 – È l'intervallo di celle in cui controlli i criteri1.
- criteri1 – È la condizione che devi controllare rispetto a criteri_intervallo1.
- criteria_range2, criterio2, …– Gli intervalli e i criteri aggiuntivi da valutare. E puoi aggiungere più intervalli e condizioni alla formula.
Utilizzeremo il set di dati nello screenshot seguente per dimostrare come funziona la funzione SOMMA.PI.SE con criteri diversi.
SOMMA CON CONDIZIONI DI TESTO
È possibile sommare i valori in base a due criteri di testo diversi in intervalli diversi. Ad esempio, supponiamo che tu voglia scoprire l'importo totale delle vendite dell'articolo Tenda consegnato. Per questo, usa questa formula:
=SOMMA.SE(D2:D13,A2:A13,"Tenda",C2:C13,"Consegnato")In questa formula abbiamo due criteri: “Tenda” e “Consegnata”. La funzione SUMIFS controlla l'elemento "Tenda" (criteri1) nell'intervallo A2:A13 (intervallo_criteri1) e controlla lo stato "Consegnato" (criteri2) nell'intervallo C2:C13 (intervallo_criteri2). Quando entrambe le condizioni sono soddisfatte, somma il valore corrispondente nell'intervallo di celle D2: D13 (sum_range).
SOMMA.SE con criteri numerici e operatori logici
È possibile utilizzare operatori condizionali per creare condizioni con numeri per la funzione SOMMA.PI.SE.
Per trovare le vendite totali di più di 5 quantità di qualsiasi articolo nello stato della California (CA), utilizzare questa formula:
=SOMMA.SE(E2:E13;D2:D13,">5",B2:B13,"CA")Questa formula ha due condizioni: “>5” e “CA”.
Questa formula controlla le quantità (Qtà) maggiori di 5 nell'intervallo D2:D13 e controlla lo stato "CA" nell'intervallo B2:B13. E quando entrambe le condizioni sono soddisfatte (nel senso che ci sono nella stessa riga), somma l'importo in E2:E13.
SUMIF con criteri di data
La funzione SUMIFS consente inoltre di verificare più condizioni nello stesso intervallo e in intervalli diversi.
Supponiamo di voler controllare l'importo totale delle vendite degli articoli consegnati dopo il 31/5/2021 e prima della data 10/6/2021, quindi utilizzare questa formula:
=SOMMA.SE(E2:E13;RE2:RE13,">"&SOL1, RE2:RE13,"<"&RE2, DO2:DO13, SOL3)La formula sopra ha tre condizioni: 31/5/2021,10/5/2021 e Consegnato. Invece di utilizzare valori diretti di data e testo, abbiamo fatto riferimento a celle contenenti tali criteri.
La formula controlla le date dopo il 31/5/2021 (G1) e le date prima del 10/6/2021 (G2) nello stesso intervallo D2:D13 e verifica lo stato "Consegnato" tra queste due date. Quindi, somma l'importo relativo nell'intervallo E2:E13.
SUMIF con celle vuote e non vuote
A volte, potresti voler trovare la somma dei valori quando una cella corrispondente è vuota o meno. Per fare ciò, puoi utilizzare uno dei tre criteri di cui abbiamo discusso prima: "=", "" e "".
Ad esempio, se desideri sommare solo la quantità di articoli "Tenda" per i quali la data di consegna non è stata ancora confermata (celle vuote), puoi utilizzare il criterio "=":
=SOMMA.SE(D2:D13,A2:A13,"Tenda",C2:C13,"=")La formula cerca l'elemento "Tenda" (criteri1) nella colonna A con le celle vuote corrispondenti (criteri2) nella colonna C e quindi somma l'importo corrispondente nella colonna D. Il "=" rappresenta una cella completamente vuota.
Per trovare l'importo totale degli articoli "Tenda" per i quali è stata confermata la data di consegna (non celle vuote), utilizzare "" come criterio:
=SOMMA.SE(D2:D13;A2:A13,"Tenda",C2:C13,"")Abbiamo appena scambiato "=" per "" in questa formula. Trova la somma degli elementi della tenda con celle non vuote nella colonna C.
SOMMA CON OR Logic
Poiché la funzione SOMMA.SE funziona sulla logica AND, somma solo quando tutte le condizioni sono soddisfatte. Ma cosa succede se si desidera sommare il valore in base a più criteri quando uno dei criteri viene soddisfatto. Il trucco consiste nell'usare più funzioni SOMMA.PI.SE.
Ad esempio, se desideri sommare l'importo delle vendite per "Portabiciclette" OPPURE per "Zaino" quando il loro stato è "Ordinato", prova questa formula:
=SUMIFS(D2:D13,A2:A13,"Portabici",C2:C13,"Ordinato") +SUMIFS(D2:D13,A2:A13,"Zaino",C2:C13,"Ordinato")La prima funzione SUMIFS controlla due criteri "Portabici" e "Ordinato" e somma i valori degli importi nella colonna D. Quindi, la seconda SUMIFS controlla due criteri "Zaino" e "Ordinato" e somma i valori degli importi nella colonna D. E poi , entrambe le somme vengono sommate e visualizzate su F3. In parole semplici, questa formula somma quando viene ordinato "Portabiciclette" o "Zaino".
Questo è tutto ciò che devi sapere sulla funzione SOMMA.SE e SOMMA.SE in Fogli Google.