Formula generica
=SUMPRODUCT(data*(headers=A1))
Sommario
Per sommare i valori nelle colonne facendo corrispondere le intestazioni di colonna corrispondenti, puoi utilizzare una formula basata sulla funzione SUMPRODUCT. Nell'esempio mostrato, la formula in J5 è:
=SUMPRODUCT(data*(LEFT(headers)=J4))
dove "dati" è l'intervallo denominato B5: G14 e "intestazioni" è l'intervallo denominato B4: G4.
La formula somma le colonne in cui le intestazioni iniziano con "a" e restituisce 201.
Spiegazione
Fondamentalmente, questa formula si basa sulla funzione SUMPRODUCT per sommare i valori nelle colonne corrispondenti nell'intervallo denominato "dati" C5: G14. Se tutti i dati fossero forniti a SUMPRODUCT in un unico intervallo, il risultato sarebbe la somma di tutti i valori nell'intervallo:
=SUMPRODUCT(data) // all data, returns 387
Per applicare un filtro facendo corrispondere le intestazioni di colonna - colonne con intestazioni che iniziano con "A" - utilizziamo la funzione SINISTRA in questo modo:
LEFT(headers)=J4) // must begin with "a"
Questa espressione restituisce TRUE se un'intestazione di colonna inizia con "a" e FALSE in caso contrario. Il risultato è un array:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Puoi vedere che i valori 1,2 e 5 corrispondono alle colonne che iniziano con "a".
All'interno di SUMPRODUCT, questo array viene moltiplicato per "dati". A causa della trasmissione, il risultato è un array bidimensionale come questo:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Se visualizziamo questo array in una tabella, è facile vedere che solo i valori nelle colonne che iniziano con "a" sono sopravvissuti all'operazione, tutte le altre colonne sono zero. In altre parole, il filtro mantiene i valori di interesse e "annulla" il resto:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Con un solo array da elaborare, SUMPRODUCT restituisce la somma di tutti i valori, 201.
Somma per corrispondenza esatta
L'esempio sopra mostra come sommare le colonne che iniziano con uno o più caratteri specifici. Per sommare la colonna in base a una corrispondenza esatta, puoi utilizzare una formula più semplice come questa:
=SUMPRODUCT(data*(headers=J4))
