Generieke formule
=SUMPRODUCT(data*(headers=A1))
Samenvatting
Als u waarden in kolommen wilt optellen door overeenkomende kolomkoppen te matchen, kunt u een formule gebruiken die is gebaseerd op de functie SOMPRODUCT. In het getoonde voorbeeld is de formule in J5:
=SUMPRODUCT(data*(LEFT(headers)=J4))
waar "data" het benoemde bereik B5: G14 is, en "headers" het benoemde bereik B4: G4.
De formule telt kolommen op waarvan kopteksten beginnen met "a" en retourneert 201.
Uitleg
In de kern is deze formule gebaseerd op de SOMPRODUCT-functie om waarden in overeenkomende kolommen in het benoemde bereik "gegevens" C5: G14 op te tellen. Als alle gegevens in een enkel bereik aan SUMPRODUCT zouden zijn verstrekt, zou het resultaat de som zijn van alle waarden in het bereik:
=SUMPRODUCT(data) // all data, returns 387
Om een filter toe te passen door kolomkoppen te matchen - kolommen met koppen die beginnen met "A" - gebruiken we de LEFT-functie als volgt:
LEFT(headers)=J4) // must begin with "a"
Deze uitdrukking geeft WAAR terug als een kolomkop begint met "a", en ONWAAR als dat niet het geval is. Het resultaat is een array:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
U kunt zien dat de waarden 1,2 en 5 overeenkomen met kolommen die beginnen met "a".
Binnen SUMPRODUCT wordt deze array vermenigvuldigd met "data". Als gevolg van uitzending is het resultaat een tweedimensionale array zoals deze:
(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)
Als we deze array in een tabel visualiseren, is het gemakkelijk te zien dat alleen waarden in kolommen die beginnen met "a" de bewerking hebben overleefd, alle andere kolommen zijn nul. Met andere woorden, het filter houdt interessante waarden bij en 'annuleert' de rest:
| 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 |
Met slechts één array om te verwerken, retourneert SOMPRODUCT de som van alle waarden, 201.
Som op exacte overeenkomst
Het bovenstaande voorbeeld laat zien hoe kolommen worden opgeteld die beginnen met een of meer specifieke tekens. Om de kolom op te tellen op basis van een exacte overeenkomst, kunt u een eenvoudigere formule als deze gebruiken:
=SUMPRODUCT(data*(headers=J4))
