Generisk formel
=SUMPRODUCT(data*(headers=A1))
Sammendrag
For å summere verdier i kolonner ved å matche matchende kolonneoverskrifter, kan du bruke en formel basert på SUMPRODUCT-funksjonen. I eksemplet vist er formelen i J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
der "data" er det navngitte området B5: G14, og "overskrifter" er det navngitte området B4: G4.
Formelen summerer kolonner der overskrifter begynner med "a" og returnerer 201.
Forklaring
I kjernen er denne formelen avhengig av SUMPRODUCT-funksjonen for å summere verdier i samsvarende kolonner i det nevnte området "data" C5: G14. Hvis alle data ble levert til SUMPRODUCT i et enkelt område, ville resultatet være summen av alle verdiene i området:
=SUMPRODUCT(data) // all data, returns 387
For å bruke et filter ved å matche kolonneoverskrifter - kolonner med overskrifter som begynner med "A" - bruker vi VENSTRE-funksjonen slik:
LEFT(headers)=J4) // must begin with "a"
Dette uttrykket returnerer SANT hvis en kolonneoverskrift begynner med "a", og FALSE hvis ikke. Resultatet er en matrise:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Du kan se at verdiene 1,2 og 5 tilsvarer kolonnene som begynner med "a".
Inne i SUMPRODUCT multipliseres denne matrisen med "data". På grunn av kringkasting er resultatet et todimensjonalt utvalg som dette:
(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)
Hvis vi visualiserer denne matrisen i en tabell, er det lett å se at bare verdier i kolonner som begynner med "a" har overlevd operasjonen, alle andre kolonner er null. Med andre ord holder filteret verdiene av interesse og "avbryter" resten:
| 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 |
Med bare en enkelt matrise å behandle, returnerer SUMPRODUCT summen av alle verdier, 201.
Sum etter eksakt samsvar
Eksemplet ovenfor viser hvordan du summerer kolonner som begynner med ett eller flere spesifikke tegn. For å summere kolonne basert på et eksakt samsvar, kan du bruke en enklere formel som denne:
=SUMPRODUCT(data*(headers=J4))
