Funzione SUMIFS, come utilizzare la funzione per sommare più condizioni in Excel

La funzione Sumifs in Excel è estremamente utile quando devi sommare molte condizioni. Di seguito sono riportati i dettagli su come utilizzare la funzione Sumif in Microsoft Excel .

La funzione SOMMA.FS in Excel è una delle funzioni di base di Excel , una funzione di calcolo comunemente utilizzata in Excel. Per calcolare il totale in Excel utilizzeremo la funzione SOMMA , mentre se vogliamo aggiungere una determinata condizione alla funzione totale utilizzeremo la funzione SOMMA.SE . Nei casi in cui la tabella dati richiede una somma con molte condizioni, dobbiamo utilizzare la funzione SUMIFS. L'articolo seguente ti guiderà su come utilizzare la funzione SOMMA.FS in Excel.

• MS Excel - Lezione 5: Formule e funzioni di Excel
• Come combinare la funzione Sumif e la funzione Vlookup in Excel
• 3 modi per calcolare i totali in Excel
• Come correggere l'errore della funzione SOMMA che non aggiunge in Excel

Indice dell'articolo

• Sintassi della funzione SOMMA.FS in Excel
• Nota quando si utilizza la funzione SOMMA.FS in Excel
• Istruzioni per l'utilizzo della funzione SOMMA.FS in Excel
  • Usa i caratteri jolly
  • Utilizzare gli intervalli denominati con la funzione SOMMA.FS
  • Esempio della funzione SOMMA.FS in Excel
• Limitazioni della funzione SOMMA.FS

Sintassi della funzione SOMMA.FS in Excel

La sintassi della funzione SUMIFS in Excel è =SUMIFS(intervallo_somma,intervallo_criteri1,criteri1,intervallo_criteri2,criteri2,…).

Lì dentro:

• Sum_range: sono le celle che devono essere sommate nella tabella dati, i valori vuoti e i valori di testo vengono ignorati, parametri obbligatori.
• Intervallo_criteri1: l'intervallo che deve essere testato utilizzando il criterio criteri1, è un valore obbligatorio.
• Criteria1: condizione applicata a intervallo_criteri1, può essere un numero, un'espressione, un riferimento di cella per determinare quali celle in intervallo_criteri1 verranno sommate, anche un valore richiesto.
• Intervallo_criteri2, criteri2,…: intervalli e condizioni aggiuntivi opzionali, massimo 127 coppie di intervallo_criteri, criteri.

Nota quando si utilizza la funzione SOMMA.FS in Excel

• Per ogni cella nell'intervallo, l'intervallo sum_range verrà sommato quando vengono soddisfatte tutte le condizioni corrispondenti determinate come vere per quella cella.
• Le celle in sum_range che contengono TRUE sono considerate 1, le celle che contengono FALSE sono considerate 0.
• Ogni intervallo_criteri deve avere la stessa dimensione di selezione di intervallo_somma, intervallo_criteri e intervallo_somma devono avere lo stesso numero di righe e lo stesso numero di colonne.
• I criteri possono utilizzare un punto interrogativo (?) invece di un singolo carattere, un asterisco (*) invece di una stringa. Se la condizione è un punto interrogativo o un asterisco, è necessario inserire un segno ~ davanti, il valore della condizione è il testo racchiuso tra ".
• Vengono applicate più condizioni utilizzando la logica AND, ovvero criteri1 AND criteri2, ecc.
• Ogni intervallo aggiuntivo deve avere lo stesso numero di righe e colonne dell'intervallo totale, ma non è necessario che gli intervalli siano contigui. Se fornisci intervalli non corrispondenti, riceverai un errore #VALUE.
• La stringa di testo nei criteri deve essere racchiusa tra virgolette (""), ad esempio "apple", ">32", "jap*"
• I riferimenti di cella nei criteri non possono essere racchiusi tra virgolette, ad esempio "<>
• SOMMA.SE e SUMIFS possono gestire intervalli, ma non matrici. Ciò significa che non puoi utilizzare altre funzioni come ANNO nell'intervallo di criteri perché il risultato è un array. Se hai bisogno di questa funzionalità, utilizza la funzione SUMPRODUCT.
• L'ordine degli argomenti tra le funzioni SOMMA.FS e SOMMA.SE è diverso. L'intervallo della somma è il primo argomento in SOMMA.FS, ma il terzo argomento in SOMMA.SE.

Istruzioni per l'utilizzo della funzione SOMMA.FS in Excel

Usa i caratteri jolly

Caratteri jolly come "*" e "?" può essere utilizzato nell'argomento criteri quando si utilizza la funzione SOMMA.FS. L'uso di questi caratteri jolly ti aiuterà a trovare corrispondenze simili ma non esatte.

Asterisco (*): corrisponde a qualsiasi stringa di caratteri, che può essere utilizzata dopo, prima o con criteri circostanti per consentire l'uso parziale dei criteri di ricerca.

Ad esempio, se applichi i seguenti criteri nella funzione SOMMA.FS:

• N * - Implica tutte le celle nell'intervallo che inizia con N
• * N - Implica tutte le celle nell'intervallo che termina con N
• *N* - Celle contenenti N

Punto interrogativo (?): corrisponde a qualsiasi singolo carattere. Supponiamo di applicare N?r come criterio. "?" qui sostituirà un singolo carattere. N?r corrisponderà a Nord, Nor, ecc. Tuttavia, non prenderà in considerazione il Nome.

Cosa succede se i dati forniti contengono un asterisco o un vero e proprio punto interrogativo?

In questo caso puoi usare la tilde (~). In questo caso è necessario digitare "~" prima del punto interrogativo.

Utilizzare gli intervalli denominati con la funzione SOMMA.FS

Intervallo denominato è il nome descrittivo di un insieme di celle o di un intervallo di celle in un foglio di lavoro. È possibile utilizzare gli intervalli denominati durante l'utilizzo della funzione SOMMA.FS.

Esempio della funzione SOMMA.FS in Excel

Calcoleremo il totale con la tabella dati seguente con una serie di condizioni diverse.

Esempio 1 : calcola i prodotti totali venduti dal dipendente di Hoai con un prezzo unitario inferiore a 400.000 VND.

Nella casella di immissione del risultato, inseriamo la formula =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") e poi="" premi="">

Lì dentro:

• D2:D7 è l'area in cui deve essere calcolato l'articolo totale.
• C2:C7 è l'intervallo di valori della condizione del nome del dipendente.
• “Hoai” è la condizione per il nome del dipendente che deve calcolare il numero di elementi contenuti nell'area C2:C7.
• E2:E7 è la condizione dell'articolo.
• “400.000” è la condizione per contenere oggetti nelle aree E2:E7.

Il risultato è il numero totale di elementi come mostrato di seguito.

Esempio 2 : Calcola il numero totale di articoli venduti dai dipendenti russi con numero di serie <>

Nella casella di immissione del risultato, inseriamo la formula =SUMIFS(D2:D7,C2:C7,"=Russia",A2:A7,"<5") e quindi="" premi="" invio.="" in = "" nome="" moltiplicazione="" tavoletta="" "nga"="" inserisci="" segno="o" rimuovi="" segno="pari">

Di conseguenza, otteniamo il numero totale di articoli venduti dai dipendenti russi con numeri di serie < 5="" ovvero 550 articoli="">

Esempio 3 : Calcola il numero totale di prodotti venduti dai dipendenti russi, esclusi i prodotti sciarpe.

Nella casella di immissione del risultato, inseriamo la formula =SUMIFS(D2:D7,C2:C7,“Russia”,B2:B7,“<>sciarpa”). In cui il segno <> viene utilizzato per escludere come condizione un determinato oggetto nell'area dati.

Il risultato è il numero totale di prodotti come mostrato di seguito.

Esempio 4:

Calcola il totale di tutte le statistiche per i Pokemon di tipo Acqua di prima generazione:

La condizione qui è che Acqua e Generazione siano 1.

Nota: il set di dati completo continua dopo la riga 14, fino alla riga 759.

Ecco i dettagli passo passo:

1. Seleziona la cella H3 .
2. Digitare =SOMMA.SE.
3. Fare doppio clic sul comando SOMMA.FS.
4. Selezionare l'intervallo totale come C2:C759.
5. Tipo,
6. Seleziona l'intervallo per la prima condizione B2:B759(valore di tipo 1)
7. Seleziona i criteri (cella F3, con valore Water)
8. tipo ,
9. Seleziona l'intervallo per la seconda condizione da D2:D759(Valore di generazione)
10. Tipo,
11. Determinare i criteri (la cella G3 ha valore 1).
12. Premere Invio .

Nota: è possibile aggiungere ulteriori condizioni ripetendo i passaggi da 9 a 12 prima di premere Invio.

Questa funzione ora calcola la somma delle statistiche totali per i Pokemon d'acqua di prima generazione. Questa funzione può essere ripetuta affinché le generazioni future possano confrontarle.

Sopra sono riportati alcuni esempi di come utilizzare la funzione SOMMA.FS, calcolando il valore totale con molte condizioni combinate. Dobbiamo organizzare correttamente gli intervalli e le condizioni di accompagnamento in modo che Excel possa riconoscere la formula della funzione per il calcolo.

Limitazioni della funzione SOMMA.FS

La funzione SOMMA.FS presenta alcune limitazioni di cui è necessario essere consapevoli:

• Le condizioni in SUMIFS vengono combinate utilizzando la logica AND. In altre parole, tutte le condizioni devono essere VERE affinché una cella venga inclusa nel totale. Per sommare le celle utilizzando la logica OR, nei casi semplici è possibile utilizzare una soluzione alternativa.
• La funzione SOMMA.FS richiede intervalli effettivi per tutti gli argomenti di intervallo; non è possibile utilizzare un array. Ciò significa che non puoi fare cose come estrarre l'anno da una data all'interno della funzione SUMIFS. Per modificare i valori che compaiono nell'argomento intervallo prima di applicare i criteri, la funzione SUMPRODUCT è una soluzione flessibile.
• SUMIFS non fa distinzione tra maiuscole e minuscole. Per sommare i valori in base a una condizione con distinzione tra maiuscole e minuscole, è possibile utilizzare una formula basata sulla funzione SUMPRODUCT con la funzione EXACT.

Il modo più comune per aggirare le limitazioni di cui sopra consiste nell'utilizzare la funzione SUMPRODUCT. Nelle versioni attuali di Excel, un'altra opzione consiste nell'utilizzare le funzioni BYROW e BYCOL più recenti.

Come correggere l'errore #VALORE nella funzione SUMIFS di Excel

Problema: la formula si riferisce alle celle di una cartella di lavoro chiusa

La funzione SOMMA.SE che fa riferimento a una cella o a un intervallo di celle in una cartella di lavoro chiusa genererà un errore #VALORE!.

Nota: questo è un errore comune. Questo messaggio si verifica spesso anche quando si utilizzano altre funzioni di Excel come CONTA.SE, CONTA.SE, CONTA.VUOTO...

Soluzione:

Apri la cartella di lavoro contenuta in questa formula, premi F9 per aggiornare la formula. Puoi anche risolvere il problema utilizzando entrambe le funzioni SOMMA e SE in una formula di matrice.

Problema: la stringa dei criteri supera i 255 caratteri

La funzione SOMMA.FS restituisce risultati errati quando si tenta di combinare stringhe più lunghe di 255 caratteri.

Soluzione:

Se possibile, accorciare la corda. Se ciò non è possibile, utilizza la funzione CONCATENATE o l'operatore & per dividere il valore in più stringhe. Per esempio:

=SUMIF(B2:B12,"long string"&"another long string")

Problema: in SUMIFS, l'argomento intervallo_criteri non è coerente con l'argomento intervallo_sum.

L'argomento intervallo deve essere sempre lo stesso in SUMIFS. Ciò significa che gli argomenti intervallo_criteri e intervallo_somma dovrebbero fare riferimento allo stesso numero di righe e colonne.

Nell'esempio seguente, la formula restituisce il totale delle vendite giornaliere di mele a Bellevue. Tuttavia, l'argomento intervallo_somma (C2:C10) non corrisponde al numero di righe e colonne in intervallo_criteri (A2:A12 e B2:B12). L'utilizzo di una formula =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)genererà un errore #VALORE.

Soluzione:

Passare sum_rangea C2:C12 e riprovare la formula.

Ti auguro successo!