Generisk formel
=SUMPRODUCT(expression,range)
Sammanfattning
För att filtrera resultat av SUMPRODUCT med specifika kriterier kan du använda enkla logiska uttryck direkt på matriser i funktionen istället för att använda IF-funktionen. I exemplet som visas är formlerna i H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
där följande namngivna områden definieras:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Om du hellre vill undvika namngivna intervall, använd ovan angivna intervall som absoluta referenser. De logiska uttrycken i H6 och H7 kan kombineras, som förklaras nedan.
Förklaring
Detta exempel illustrerar en av nyckelstyrkorna i SUMPRODUCT-funktionen - möjligheten att filtrera data med grundläggande logiska uttryck istället för IF-funktionen. Inuti SUMPRODUCT är den första matrisen ett logiskt uttryck för att filtrera på färgen "röd":
--(color="red")
Detta resulterar i en array eller TRUE FALSE-värden, som tvingas till en och nollor med den dubbla negativa (-) operationen. Resultatet är denna array:
(1;0;1;0;0;0;1;0;0;0)
Observera att matrisen innehåller 10 värden, ett för varje rad. En anger en rad där färgen är "röd" och en noll anger en rad med någon annan färg.
Därefter har vi ytterligare två matriser: en för kvantitet och en för pris. Tillsammans med detta resultat från den första matrisen har vi:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Utvidgning av matriserna har vi:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
SUMPRODUCTs kärnbeteende är att multiplicera, sedan summera matriser. Eftersom vi arbetar med tre matriser kan vi visualisera operationen som visas i tabellen nedan, där resultatkolumnen är resultatet av att multiplicera array1 * array2 * array3 :
| array1 | array2 | array3 | resultat |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Notice array1 fungerar som ett filter - nollvärden här "noll ut" värden i rader där färgen inte är "röd". Genom att sätta tillbaka resultaten i SUMPRODUCT har vi:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Vilket ger ett slutresultat på 480.
Lägga till ytterligare kriterier
Du kan utöka kriterierna genom att lägga till ett annat logiskt uttryck. Till exempel, för att hitta den totala försäljningen där färgen är "röd" och staten är "TX", innehåller H6:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Obs! SUMPRODUCT är inte skiftlägeskänslig.
Förenkla med en enda array
Excel-proffs förenklar ofta syntaxen i SUMPRODUCT lite genom att multiplicera arrayer direkt inuti array1 så här:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Detta fungerar eftersom matteoperationen (multiplikation) automatiskt tvingar fram SANT och FALSKA värden från de två första uttrycken till en och nollor.
