Scarica la cartella di lavoro di esempio
Questo tutorial mostrerà come calcolare “sumproduct if”, restituendo la somma dei prodotti di array o range in base a criteri.
Funzione SUMPRODOTTO
La funzione SUMPRODUCT viene utilizzata per moltiplicare array di numeri, sommando l'array risultante.
Per creare un "Sumproduct If", utilizzeremo la funzione SUMPRODUCT insieme alla funzione SE in una formula di matrice.
SOMMAPRODOTTO SE
Combinando SUMPRODUCT e SE in una formula di matrice, possiamo essenzialmente creare una funzione "SUMPRODUCT IF" che funziona in modo simile a come funziona la funzione SOMMA.SE incorporata. Passiamo attraverso un esempio.
Abbiamo un elenco delle vendite realizzate da manager in diverse regioni con corrispondenti tassi di commissione:
Supponiamo che ci venga chiesto di calcolare l'importo della commissione per ciascun manager in questo modo:
Per fare ciò, possiamo annidare una funzione SE con il manager come i nostri criteri all'interno della funzione SUMPRODUCT in questo modo:
=SOMMA.PRODOTTO(SE(=,*))
=SOMMA.PRODOTTO(SE($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
Quando si utilizza Excel 2022 e versioni precedenti, è necessario inserire la formula premendo CTRL + MAIUSC + INVIO per ottenere le parentesi graffe attorno alla formula (vedi immagine in alto).
Come funziona la formula?
La formula funziona valutando ogni cella nel nostro intervallo di criteri come VERO o FALSO.
Calcolo della commissione totale per Olivia:
=SOMMA.PRODOTTO(SE($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))
= SUMPRODUCT (SE({VERO; VERO; FALSO; FALSO; FALSO; VERO; FALSO; FALSO; FALSO}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
Successivamente, la funzione IF sostituisce ogni valore con FALSE se la sua condizione non è soddisfatta.
= SUMPRODOTTO({928.62; 668.22; FALSO; FALSO; FALSO; 480.564; FALSO; FALSO; FALSO})
Ora la funzione SUMPRODUCT salta i valori FALSE e somma i valori rimanenti (2.077,40).
SUMPRODUCT SE con più criteri
Per utilizzare SUMPRODUCT SE con più criteri (in modo simile a come funziona la funzione SUMIFS incorporata), nidifica semplicemente più funzioni SE nella funzione SUMPRODUCT in questo modo:
=SOMMA.PRODOTTO(SE(=, SE(=, *))
(CTRL + MAIUSC + INVIO)
=SOMMAPRODOTTO(SE($B$2:$B$10=$G2,SE($C$2:$C$10=$H2,$D$2:$D$10*$E$2:$E$10)))
(CTRL+MAIUSC+INVIO)
Un altro approccio a SUMPRODUCT SE
Spesso in Excel ci sono diversi modi per ottenere i risultati desiderati. Un modo diverso per calcolare "sumproduct if" consiste nell'includere i criteri entro la funzione SUMPRODUCT come un array usando il doppio unario in questo modo:
=SOMMAPRODOTTO(--($B$2:$B$10=$G2),--($C$2:$C$10=$H2),$D$2:$D$10*$E$2:$E$10)
Questo metodo utilizza il doppio unario (-) per convertire un array VERO FALSO in zero e uno. SUMPRODUCT quindi moltiplica gli array di criteri convertiti insieme:
=SOMMA.PRODOTTO({1;1;0;0;0;1;0;0;0},{1;0;1;0;1;0;0;0;0},{928.62; 668.22;919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Suggerimenti e trucchi:
- Ove possibile, blocca sempre il riferimento (F4) agli intervalli e agli input della formula per consentire il riempimento automatico.
- Se stai utilizzando Excel 2022 o successivo, puoi inserire la formula senza Ctrl + Maiusc + Invio.
SOMMAPRODOTTO SE in Fogli Google
La funzione SUMPRODUCT IF funziona esattamente allo stesso modo in Fogli Google come in Excel:
