SUMPRODUCT med IF

För att filtrera resultaten 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 (expression,range)

där följande namngivna intervall är definierad:

 
= SUMPRODUCT (--(color='red'),quantity,price) = SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price) = SUMPRODUCT (--(state='co'),--(color='blue'),quantity,price)

Om du hellre vill undvika namngivna intervall använder du de intervall som anges ovan som absoluta referenser . De logiska uttrycken i H6 och H7 kan kombineras, såsom förklaras nedan.

Detta exempel illustrerar en av de viktigaste styrkorna för SUMPRODUCT -funktionen - möjligheten att filtrera data med grundläggande logiska uttryck istället för IF -funktionen. Inuti SUMPRODUCT, den första array är ett logiskt uttryck för att filtrera på färgen 'röd':

 
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14

Detta resulterar i en array eller TRUE FALSE -värden, som tvingas till enor och nollor med dubbel negativ (-) drift. Resultatet är denna array:

 
--(color='red')

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 nolla indikerar 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:

 
{1010001000}

Vi utökar matriserna och har:

 
= SUMPRODUCT ({1010001000},quantity,price)

SUMPRODUCTs kärnbeteende är att multiplicera och 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 :

Lägga märke till array1 fungerar som ett filter - nollvärden här 'noll -ut' -värden i rader där färgen inte är 'röd'. När vi lägger tillbaka resultaten i SUMPRODUCT har vi:

ta bort de tre sista tecknen från strängen

 
= SUMPRODUCT ({1010001000},{1061491110891110},{15181516181815161816})

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 total försäljning där färgen är 'Röd' och staten är 'TX', innehåller H6:

 
= SUMPRODUCT ({1500210000120000})

Obs! SUMPRODUCT är inte skiftlägeskänsligt.

Förenkling med en enda array

Excel -proffs kommer ofta att förenkla syntaxen inuti SUMPRODUCT lite genom att multiplicera matriser direkt inuti array1 så här:

 
= SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price)

Detta fungerar eftersom matematikoperationen (multiplikation) automatiskt tvingar de Sanna och FALSKA värdena från de två första uttrycken till ettor och nollor.