Come utilizzare COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Le tre formule più comunemente utilizzate in Excel che eseguono semplici calcoli matematici sono COUNT , SUM e AVERAGE . Che tu stia gestendo un budget finanziario in Excel o semplicemente tenendo traccia della tua prossima vacanza, probabilmente hai già utilizzato una di queste funzioni.

In questo articolo, esamineremo le basi di queste tre funzioni e le loro controparti rilevanti e utili: CONTA.SE, SOMMA.SE e MEDIA.SE.

Diciamo che stiamo avviando una nuova attività online di vendita di telefoni cellulari e abbiamo un foglio che elenca le vendite che abbiamo effettuato nei primi due mesi. Scarica il foglio di calcolo Excel di esempio qui .

Excel CONTEGGIO, SOMMA e MEDIA

Per sapere quanti telefoni cellulari abbiamo venduto, possiamo utilizzare rapidamente la formula COUNT come mostrato di seguito:

=COUNT(E2:E16)

D'altra parte, per ottenere l'importo totale delle vendite che abbiamo realizzato, possiamo utilizzare la formula SUM come mostrato di seguito:

=SOMMA(E2:E16)

Infine, per scoprire le vendite medie che abbiamo realizzato per tutti i telefoni, possiamo utilizzare la formula MEDIA come di seguito:

=MEDIA(E2:E16)

Il risultato dovrebbe essere il seguente:

Le formule COUNT, SUM e AVERAGE funzioneranno solo per i record in cui il valore della cella è in formato numerico. Qualsiasi record all'interno dell'intervallo della formula (ad esempio E2:E16 in questo esempio) non nel formato numerico verrà ignorato.

Quindi, assicurati che tutte le celle all'interno della formula COUNT, SUM e AVERAGE siano tutte formattate come Number , non Text . Prova a utilizzare la stessa formula, ma con E:E come intervallo invece di E2:E16 . Restituirà lo stesso risultato di prima perché ignora l'intestazione (ad esempio Sale Price ), che è in formato testo.

Ora, cosa succede se vogliamo conoscere il numero di vendite, l'importo totale delle vendite e l'importo medio delle vendite per telefono, solo per quelli venduti negli Stati Uniti? È qui che COUNTIFS, SUMIFS e AVERAGEIFS giocano un ruolo importante. Osserva la formula qui sotto:

CONTA.SE

Ripartizione della formula:

1. =COUNTIFS( – Il "=" indica l'inizio di una formula nella cella e COUNTIFS è la prima parte della funzione di Excel che stiamo utilizzando.
2. D2:D16 – Si riferisce all'intervallo di dati da verificare per vedere se soddisfa i criteri da includere nella formula di conteggio.
3. “USA” – Criteri da cercare nell'intervallo di dati specificato ( D2:D16 )
4. ) – Parentesi di chiusura che indica la fine della formula.

La formula restituisce 6 che è il numero di vendite per i prodotti spediti dal magazzino USA.

SOMMA.SE

Ripartizione della formula:

1. =SUMIFS( – Il "=" indica nuovamente l'inizio della formula.
2. E2:E16 – Si riferisce all'intervallo di dati che vorremmo totalizzare, ad esempio il prezzo di vendita nel nostro esempio.
3. D2:D16 – Si riferisce all'intervallo di dati da verificare per vedere se soddisfa i criteri da includere nell'importo totale.
4. “USA” – Criteri da cercare nell'intervallo di dati specificato ( D2:D16 )
5. ) – Parentesi di chiusura che indica la fine della formula.

La formula mostra le vendite totali di $ 6.050 effettuate per i prodotti spediti dal magazzino USA.

MEDIASE

Ripartizione della formula:

1. =MEDIA.SE( – Il “=” indica l'inizio della formula.
2. E2:E16 – Si riferisce all'intervallo di dati di cui vorremmo fare la media. In questo esempio, vogliamo ottenere l'importo medio delle vendite per tutti i telefoni venduti negli Stati Uniti.
3. D2:D16 – Si riferisce all'intervallo di dati da verificare per vedere se soddisfa i criteri da includere nella formula media.
4. “USA” – Criteri da cercare nell'intervallo di dati specificato
5. ) – Parentesi di chiusura che indica gli estremi della formula.

La formula mostra che abbiamo venduto il prodotto per circa $ 1.008 per telefono negli Stati Uniti.

Tutte e tre le formule possono accettare più di un criterio. Ad esempio se vogliamo conoscere le stesse cifre (ovvero COUNT , SUM e AVERAGE ) per i prodotti venduti in USA , ma nello specifico solo per il  marchio Samsung , basterà sommare l'intervallo di dati da controllare seguito dai relativi criteri.

Vedere l'esempio di seguito in cui viene aggiunto un secondo criterio ai controlli dei criteri iniziali. (Il testo blu indica il primo criterio e il rosso indica il secondo criterio)

=CONTA.SE(D2:D16,"USA", B2:B16,"Samsung") =SOMMA.SE(E2:E16,D2:D16,"USA", B2:B16,"Samsung") =MEDIA.SE(E2:E16,D2 :D16,"USA", B2:B16,"Samsung")

Noterai che Excel ha anche  le formule COUNTIF , SUMIF e AVERAGEIF senza il suffisso "S" . Questi sono usati in modo simile a COUNTIFS , SUMIFS e AVERAGEIFS . Tuttavia, quelli senza il suffisso "S" nella formula hanno la limitazione di consentire un solo criterio per formula.

Poiché la sintassi è leggermente diversa, consiglierei di utilizzare  COUNTIFS , SUMIFS e AVERAGEIFS solo in quanto può essere utilizzato per uno o più criteri, se necessario. Divertiti!