Formula generica
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Sommario
Per sommare i valori nelle colonne e nelle righe corrispondenti, è possibile utilizzare la funzione SUMPRODUCT. Nell'esempio mostrato, la formula in J6 è:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
dove dati (C5: G14), giorni (B5: B14) e codici (C4: G4) sono intervalli denominati.
Spiegazione
La funzione SUMPRODUCT è in grado di gestire gli array in modo nativo, senza richiedere l'immissione del control shift.
In questo caso, stiamo moltiplicando tutti i valori nei dati dell'intervallo denominato per due espressioni che filtrano i valori non di interesse. La prima espressione applica un filtro basato sui codici:
(codes=J4)
Poiché J4 contiene "A002", l'espressione crea un array di valori TRUE FALSE come questo:
(FALSE,TRUE,FALSE,FALSE,FALSE)
La seconda espressione filtra il giorno:
(days=J5)
Poiché J4 contiene "Wed", l'espressione crea un array di valori TRUE FALSE come questo:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
In Excel, i valori VERO FALSO vengono automaticamente convertiti ai valori 1 e 0 da qualsiasi operazione matematica, quindi l'operazione di moltiplicazione forza gli array sopra a uno e zero e crea un array 2D con le stesse dimensioni dei dati originali. Il processo può essere visualizzato come mostrato di seguito:
Infine, SUMPRODUCT restituisce la somma di tutti gli elementi nell'array finale, 9.
Conta invece di somma
Se desideri contare i valori corrispondenti invece di sommarli, puoi abbreviare la formula in:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Nota che questo conteggio includerà celle vuote.
Appunti
- Sebbene l'esempio mostri solo una colonna con corrispondenza, questa formula sommerà correttamente più colonne con corrispondenza.
- Se hai solo bisogno di abbinare le colonne (non le righe) puoi usare una formula come questa.
- Per abbinare solo le righe, puoi utilizzare la funzione COUNTIFS.
