Come utilizzare COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Le tre formule più comunemente utilizzate in Excel che eseguono semplici calcoli matematici sono CONTARE, SOMMA e MEDIA. Sia che tu stia gestendo un budget finanziario in Excel o semplicemente tenendo traccia delle tue prossime vacanze, probabilmente hai già utilizzato una di queste funzioni.

In questo articolo, esamineremo le basi di queste tre funzioni e le loro controparti utili e pertinenti: COUNTIFS, SUMIFS e AVERAGEIFS.

Diciamo che stiamo iniziando un nuovo business online che vende telefoni cellulari e abbiamo un foglio che elenca le vendite che abbiamo fatto nei primi due mesi. Scarica esempio foglio di calcolo Excel qui.

Excel COUNT, SUM e MEDIA

Per sapere quanti telefoni cellulari abbiamo venduto, possiamo usare rapidamente il CONTARE formula come mostrato di seguito:

= COUNT (E2: E16)

D'altra parte, per ottenere l'importo totale delle vendite che abbiamo fatto, possiamo usare il SOMMA formula come mostrato di seguito:

= SUM (E2: E16)

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

= MEDIA (E2: E16)

Il risultato dovrebbe essere il seguente:

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

Pertanto, assicurati che tutte le celle della formula COUNT, SUM e MEDIA siano tutte formattate come Numero, non Testo. Prova ad usare la stessa formula, ma con E: E come la gamma invece di E2: E16. Restituirà lo stesso risultato di prima perché ignora l'intestazione (ad es. Prezzo di vendita), 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 svolgono un ruolo importante. Osserva la seguente formula:

CONTA.PIÙ.SE

Ripartizione della formula:

1. = CONTA.PIÙ.SE ( - Il “=” indica l'inizio di una formula nella cella e CONTA.PIÙ.SE è la prima parte della funzione di Excel che stiamo usando.
2. D2: D16 - Si riferisce all'intervallo di dati da verificare per vedere se soddisfa i criteri da includere nella formula di conteggio.
3. "STATI UNITI D'AMERICA" - 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.PIÙ.SE

Ripartizione della formula:

1. = SUMIFS ( - Il “=” indica di nuovo l'inizio della formula.
2. E2: E16 - Fa riferimento alla gamma di dati che vorremmo totalizzare, ovvero il prezzo di vendita nel nostro esempio.
3. D2: D16 - Fa riferimento alla gamma di dati da verificare per vedere se soddisfa i criteri da includere nell'importo totale.
4. "STATI UNITI D'AMERICA" - Criteri da cercare nell'intervallo di dati specificato (D2: D16)
5. ) - Parentesi di chiusura che indica la fine della formula.

La formula mostra $ 6.050 vendite totali che sono state fatte per i prodotti spediti dal magazzino USA.

MEDIA.PIÙ.SE

Ripartizione della formula:

1. = MEDIA.PIÙ.SE ( - Il “=” indica l'inizio della formula.
2. E2: E16 - Fa riferimento alla gamma di dati che vorremmo mediare. In questo esempio, vogliamo ottenere la quantità media di vendite per tutti i telefoni venduti negli Stati Uniti.
3. D2: D16 - Si riferisce all'intervallo di dati da controllare per vedere se soddisfa i criteri da includere nella formula media.
4. "STATI UNITI D'AMERICA" - Criteri da cercare nell'intervallo di dati specificato
5. ) - Parentesi di chiusura che indica le estremità 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 richiedere più di un criterio. Per esempio se vogliamo conoscere le stesse figure (es. CONTARE, SOMMA e MEDIA) per i prodotti venduti in Stati Uniti d'America, ma specificamente solo per il Samsung marchio, abbiamo solo bisogno di aggiungere l'intervallo di dati da controllare seguito dai suoi criteri.

Si prega di 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)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Noterai che anche Excel ha COUNTIF, SUMIF e AVERAGEIF formule senza il suffisso "S". Quelli sono usati simili a CONTA.PIÙ.SE, SOMMA.PIÙ.SE e MEDIA.PIÙ.SE. Tuttavia, quelli senza il suffisso "S" nella formula hanno la limitazione di consentire solo un criterio per formula.

Poiché la sintassi è leggermente diversa, ti consiglio di utilizzare CONTA.PIÙ.SE, SOMMA.PIÙ.SE e MEDIA.PIÙ.SE solo come può essere usato per uno o più criteri, se necessario. Godere!