Generisk formel
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Resumé
For at opsummere værdier i matchende kolonner og rækker kan du bruge funktionen SUMPRODUCT. I det viste eksempel er formlen i J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
hvor data (C5: G14), dage (B5: B14) og koder (C4: G4) er navngivet områder.
Forklaring
SUMPRODUCT-funktionen kan håndtere arrays indbygget uden at kræve kontrolskiftindtastning.
I dette tilfælde multiplicerer vi alle værdier i de navngivne områdedata med to udtryk, der filtrerer værdier, der ikke er af interesse, ud. Det første udtryk anvender et filter baseret på koder:
(codes=J4)
Da J4 indeholder "A002", opretter udtrykket en matrix med SANDE FALSKE værdier som denne:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Det andet udtryk filtrerer på dagen:
(days=J5)
Da J4 indeholder "ons", opretter udtrykket en matrix med SANDE FALSKE værdier som denne:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
I Excel tvinges TRUE FALSE-værdier automatisk til 1- og 0-værdier af enhver matematisk operation, så multiplikationsoperationen tvinger arrays ovenfor til en og nuller og skaber et 2D-array med de samme dimensioner som de originale data. Processen kan visualiseres som vist nedenfor:
Endelig returnerer SUMPRODUCT summen af alle elementer i den endelige matrix 9.
Tæl i stedet for sum
Hvis du vil tælle matchende værdier i stedet for at opsummere, kan du forkorte formlen til:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Bemærk, at dette antal inkluderer tomme celler.
Bemærkninger
- Selvom eksemplet kun viser en matchet kolonne, vil denne formel korrekt sammenfatte flere matchede kolonner.
- Hvis du kun har brug for at matche kolonner (ikke rækker), kan du bruge en formel som denne.
- For kun at matche rækker kan du bruge COUNTIFS-funktionen.
