Generieke formule
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Samenvatting
Om waarden in overeenkomende kolommen en rijen bij elkaar op te tellen, kunt u de SOMPRODUCT-functie gebruiken. In het getoonde voorbeeld is de formule in J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
waarbij gegevens (C5: G14), dagen (B5: B14) en codes (C4: G4) benoemde bereiken zijn.
Uitleg
De SUMPRODUCT-functie kan native arrays verwerken, zonder dat de control shift-invoer nodig is.
In dit geval vermenigvuldigen we alle waarden in de genoemde bereikgegevens met twee uitdrukkingen die waarden uitfilteren die niet van belang zijn. De eerste uitdrukking past een filter toe op basis van codes:
(codes=J4)
Aangezien J4 "A002" bevat, maakt de uitdrukking een array van TRUE FALSE-waarden als volgt:
(FALSE,TRUE,FALSE,FALSE,FALSE)
De tweede uitdrukking filtert op dag:
(days=J5)
Omdat J4 "Wed" bevat, maakt de uitdrukking een array van TRUE FALSE-waarden als volgt:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
In Excel worden TRUE FALSE-waarden automatisch omgezet in waarden van 1 en 0 door een wiskundige bewerking, dus de vermenigvuldigingsbewerking dwingt de bovenstaande arrays naar enen en nullen en creëert een 2D-array met dezelfde afmetingen als de oorspronkelijke gegevens. Het proces kan worden gevisualiseerd zoals hieronder weergegeven:
Ten slotte retourneert SOMPRODUCT de som van alle elementen in de laatste array, 9.
Tellen in plaats van optellen
Als u overeenkomende waarden wilt tellen in plaats van optellen, kunt u de formule inkorten tot:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Merk op dat dit aantal lege cellen omvat.
Opmerkingen
- Hoewel het voorbeeld slechts één overeenkomende kolom toont, zal deze formule correct meerdere overeenkomende kolommen optellen.
- Als u alleen kolommen (geen rijen) hoeft te matchen, kunt u een formule als deze gebruiken.
- Om alleen rijen te matchen, kunt u de COUNTIFS-functie gebruiken.
