Somma celle che soddisfano più criteri con Excel SUMPRODUCT

Somma le celle che cadono tra due valori

La funzione SUMPRODUCT in Excel è una funzione molto versatile che darà risultati diversi a seconda del modo in cui la funzione è argomenti sono inseriti.

Normalmente, come suggerisce il nome, SUMPRODUCT moltiplica gli elementi di uno o più array per ottenere il loro prodotto e quindi somma o somma i prodotti insieme.

Regolando le funzioni sintassi, tuttavia, può essere utilizzato per sommare solo i dati nelle celle che soddisfano criteri specifici.

Da Excel 2007, il programma contiene due funzioni: SOMMA.SE e SUMIF - che sommerà i dati nelle celle che soddisfano uno o più criteri impostati.

A volte, tuttavia, è più facile lavorare con SUMPRODUCT quando si tratta di trovare più condizioni relative allo stesso gamma come mostrato nell'immagine sopra.

Sintassi della funzione SUMPRODUCT per sommare le celle

La sintassi utilizzata per ottenere SUMPRODUCT per sommare i dati nelle celle che soddisfano condizioni specifiche è:

= SUMPRODUCT([condizione1] * [condizione2] * [matrice])

condition1, condition2 - le condizioni che devono essere soddisfatte prima che la funzione trovi il prodotto dell'array.

array - un intervallo contiguo di celle.

Esempio: sommare dati in celle che soddisfano più condizioni

L'esempio nell'immagine sopra aggiunge i dati nelle celle nell'intervallo da D1 a E6 che sono tra 25 e 75.

Entrare nella funzione SUMPRODUCT

Poiché questo esempio usa una forma irregolare della funzione SUMPRODUCT, la funzione's la finestra di dialogo non può essere utilizzato per immettere la funzione e i suoi argomenti. Invece, la funzione deve essere digitata manualmente in a foglio di lavoro cellula.

1. Clicca sulla cella B7 nel foglio di lavoro per renderlo il Cellula attiva;
2. Inserisci la seguente formula nella cella B7:=SOMMAPRODOTTO(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))
3. La risposta 250 dovrebbe apparire nella cella B7
4. La risposta è stata ottenuta sommando i cinque numeri nell'intervallo (40, 45, 50, 55 e 60) compresi tra 25 e 75. Il cui totale è 250

Abbattere la formula SUMPRODUCT

Quando vengono utilizzate le condizioni per i suoi argomenti, SUMPRODUCT valuta ogni elemento dell'array rispetto alla condizione e restituisce a valore booleano (Vero o falso).

Ai fini dei calcoli, Excel assegna un valore di 1 per quegli elementi dell'array che sono VERI (soddisfano la condizione) e un valore di 0 per gli elementi dell'array che sono FALSE (non soddisfano la condizione).

Ad esempio, il numero 40:

• è TRUE per la prima condizione quindi un valore di 1 è assegnato nel primo array;
• è TRUE per la seconda condizione quindi un valore di 1 viene assegnato nel secondo array.

Il numero 15:

• è FALSE per la prima condizione quindi un valore di 0 è assegnato nel primo array;
• è TRUE per la seconda condizione quindi un valore di 1 viene assegnato nel secondo array.

Gli uni e gli zeri corrispondenti in ogni matrice vengono moltiplicati insieme:

• Per il numero 40 - abbiamo 1 x 1 che restituisce un valore di 1;
• Per il numero 15 - abbiamo 0 x 1 che restituisce un valore di 0.

Moltiplicare gli uno e gli zeri per l'intervallo

Questi uno e zeri vengono quindi moltiplicati per i numeri nell'intervallo A2: B6.

Questo viene fatto per darci i numeri che verranno sommati dalla funzione.

Funziona perché:

• 1 volte qualsiasi numero è uguale al numero originale
• 0 volte qualsiasi numero è uguale a 0

Quindi finiamo con:

• 1 * 40 = 40
  0 * 15 = 0
  0 * 22 = 0
  1 * 45 = 45
  1 * 50 = 50
  1 * 55 = 55
  0 * 25 = 0
  0 * 75 = 0
  1 * 60 = 60
  0 * 100 = 0

Sommando i risultati

SUMPRODUCT quindi riassume i risultati di cui sopra per trovare la risposta.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250.