Sommare in base a più condizioni con SUMIFS

Conosce già SUMIF, che somma i valori conformi a una singola regola, ad esempio tutte le vendite della regione Est. Ma le domande reali sono spesso più articolate: Quali sono state le vendite della regione Est a gennaio? In questo caso le condizioni sono due.

È qui che SUMIFS dà il meglio di sé. La S finale indica che è possibile combinare molti criteri e una riga viene aggiunta al totale solo se supera tutti i test specificati.

In questa lezione imparerà l'ordine degli argomenti, scriverà la Sua prima somma con più criteri ed eviterà gli errori più comuni.

SUMIFS inverte l'ordine che potrebbe aspettarsi da SUMIF. I numeri da sommare vengono prima, poi ogni condizione viene indicata come coppia.

• sum_range — i valori da sommare
• criteria_range1, criteria1 — il primo test
• criteria_range2, criteria2 — il secondo test

Può aggiungere coppie di intervallo e criterio fino a 127 condizioni. Legga ad alta voce lo schema seguente: somma questo, dove questo è uguale a quello e dove questo è uguale a quello.

Immagini una tabella in cui la colonna A contiene la Regione, la colonna B il Mese e la colonna C l'Importo. Desidera calcolare il totale delle vendite della regione Est a gennaio.

Gli importi da sommare si trovano in C:C. La prima condizione verifica A:A cercando "East", mentre la seconda verifica B:B cercando "January". Una riga viene conteggiata solo quando entrambe le condizioni sono vere.

Questo è l'errore più comune in SUMIFS. sum_range e ogni criteria_range devono avere dimensioni identiche: lo stesso numero di righe e di colonne.

Se il Suo sum_range è C2:C100 ma un intervallo dei criteri è A2:A99, la formula restituisce un errore #VALUE! perché le righe non corrispondono.

L'abitudine più sicura consiste nell'utilizzare le stesse righe iniziali e finali per tutti gli intervalli oppure nel riferirsi sempre a colonne intere, come A:A.

Inserire "East" tra virgolette funziona, ma un report flessibile consente all'utente di scegliere. Inserisca la regione nella cella F1 e il mese in F2, quindi utilizzi i riferimenti a queste celle come criteri.

Modificando F1 o F2, il totale viene ricalcolato immediatamente. Noti che un semplice riferimento a una cella non richiede virgolette: le virgolette servono solo per il testo letterale digitato nella formula.

I criteri non si limitano al testo esatto. Per i numeri può utilizzare gli operatori di confronto, racchiudendoli tra virgolette.

• ">100" — maggiore di 100
• "<=50" — minore o uguale a 50
• "<>0" — diverso da zero

Qui calcoliamo il totale degli importi della regione Est, ma solo per le righe in cui l'importo stesso è maggiore di 100. Noti che sum_range e un criteria_range possono fare riferimento alla stessa colonna.

Che cosa succede se la soglia si trova in una cella invece di essere digitata? Non può scrivere semplicemente ">F1": in questo modo cercherebbe il testo letterale F1. Deve invece unire l'operatore alla cella con il simbolo &.

In questo modo ">"&F1 costruisce il criterio maggiore di qualunque valore contenuto in F1. Questo metodo di concatenazione è essenziale per i report dinamici creati in base alle esigenze dell'utente.

SUMIFS è facilmente estensibile. Aggiunga una coppia di intervallo e criterio per ogni nuova regola. Supponiamo che la colonna D contenga il Venditore. Può calcolare il totale delle vendite della regione Est, effettuate a gennaio da "Maria".

Ogni condizione restringe ulteriormente il risultato. Poiché SUMIFS utilizza la logica AND, una riga deve soddisfare tutti e tre i test per essere inclusa nella somma.

I criteri testuali supportano i caratteri jolly. L'asterisco * corrisponde a un numero qualsiasi di caratteri, mentre il punto interrogativo ? corrisponde esattamente a uno.

• "North*" corrisponde a North, Northeast e Northwest
• "*east*" corrisponde a qualsiasi testo che contenga east

In questo modo si calcola il totale degli importi per qualsiasi regione che inizi con "North", una soluzione utile quando i nomi delle regioni condividono un prefisso.

Vale la pena memorizzare la differenza, perché l'ordine degli argomenti è invertito:

• SUMIF: range, criteria, [sum_range] — l'intervallo da verificare viene prima; sum_range è facoltativo e viene indicato per ultimo.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — sum_range viene sempre indicato per primo.

Suggerimento: se ha più di una condizione, scelga direttamente SUMIFS. Molte persone usano SUMIFS anche per un solo criterio, così da mantenere sempre lo stesso schema.

Quando SUMIFS restituisce 0, di solito significa che nessuna riga soddisfa tutte le condizioni, non che la formula sia errata. Verifichi la presenza di spazi nascosti nel testo, differenze di ortografia o numeri memorizzati come testo.

Un metodo diagnostico rapido consiste nel rimuovere una condizione alla volta. Se il totale compare dopo aver eliminato un criterio, significa che era proprio quel criterio a escludere tutte le righe. Questa abitudine di isolare e testare rende rapido il debug delle formule con più criteri.

Metta alla prova la Sua comprensione dell'ordine degli argomenti di SUMIFS.

Ora può calcolare il totale dei numeri che soddisfano più condizioni contemporaneamente:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — sum_range viene indicato per primo.
• Tutti gli intervalli devono avere le stesse dimensioni, altrimenti si verifica un errore #VALUE!.
• Utilizzi ">"&F1 per confrontare con una cella e * / ? per i caratteri jolly.
• Le condizioni usano la logica AND: una riga deve soddisfarle tutte.

Prossimo argomento: contare le righe che soddisfano più condizioni con COUNTIFS.