Generisk formel
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Sammanfattning
För att summera värden i matchande kolumner och rader kan du använda funktionen SUMPRODUCT. I exemplet som visas är formeln i J6:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
där data (C5: G14), dagar (B5: B14) och koder (C4: G4) heter områden.
Förklaring
SUMPRODUCT-funktionen kan hantera matriser inbyggt, utan att det krävs kontrollskift.
I det här fallet multiplicerar vi alla värden i namnet på områdesdata med två uttryck som filtrerar bort värden som inte är av intresse. Det första uttrycket tillämpar ett filter baserat på koder:
(codes=J4)
Eftersom J4 innehåller "A002" skapar uttrycket en matris med SANTA FALSKA värden så här:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Det andra uttrycket filtrerar på dagen:
(days=J5)
Eftersom J4 innehåller "ons" skapar uttrycket en matris med SANTA FALSKA värden så här:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
I Excel tvingas TRUE FALSE-värden automatiskt till 1- och 0-värden med valfri matematisk operation, så multipliceringsfunktionen tvingar matriserna ovan till enor och nollor och skapar en 2D-array med samma dimensioner som originaldata. Processen kan visualiseras enligt nedan:
Slutligen returnerar SUMPRODUCT summan av alla element i den slutliga matrisen, 9.
Räkna istället för summan
Om du vill räkna matchande värden istället för att summera kan du förkorta formeln till:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Observera att detta antal kommer att innehålla tomma celler.
Anteckningar
- Även om exemplet bara visar en matchad kolumn, summerar denna formel flera matchade kolumner korrekt.
- Om du bara behöver matcha kolumner (inte rader) kan du använda en sådan formel.
- För att bara matcha rader kan du använda COUNTIFS-funktionen.
