Summa celler som uppfyller flera kriterier med Excel SUMPRODUCT

Sumceller som faller mellan två värden

SUMPRODUCT-funktionen i Excel är en mycket mångsidig funktion som ger olika resultat beroende på hur funktionens argument anges. Normalt, som namnet antyder, multiplicerar SUMPRODUCT elementen i en eller flera matriser för att få sin produkt och lägger till eller summerar sedan produkterna. Genom att justera funktionens syntax kan den dock användas för att bara summera data i celler som uppfyller specifika kriterier. Sedan Excel 2007 har programmet innehöll två funktioner – SUMIF och SUMIFS – som summerar data i celler som uppfyller ett eller flera uppsatta kriterier. Ibland är det dock lättare att arbeta med SUMPRODUCT när det gäller att hitta flera förhållanden relaterade till samma intervall som visas i bilden ovan.

SUMPRODUCT-funktionssyntax till sumceller

Syntaxen som används för att få SUMPRODUCT att summera data i celler som uppfyller specifika villkor är: = SUMPRODUCT ([condition1] * [condition2] * [array]) villkor1, villkor2 – villkoren som måste uppfyllas innan funktionen hittar produkten i matrisen. array – ett sammanhängande cellområde

Exempel: summera data i celler som uppfyller flera villkor

Exemplet i bilden ovan lägger till data i celler i intervallet D1 till E6 som ligger mellan 25 och 75.

Ange SUMPRODUCT-funktionen

Eftersom detta exempel använder en oregelbunden form av SUMPRODUCT-funktionen kan funktionens dialogruta inte användas för att ange funktionen och dess argument. Istället måste funktionen skrivas in manuellt i en kalkylbladcell.

1. Klicka på cellen B7 i kalkylbladet för att göra den till den aktiva cellen;
2. Ange följande formel i cell B7:= SUMPRODUKT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
3. Svaret 250 ska visas i cell B7
4. Svaret nåddes genom att lägga till de fem siffrorna i intervallet (40, 45, 50, 55 och 60) som ligger mellan 25 och 75. Totalt är 250

Att bryta ner SUMPRODUCT-formeln

När villkor används för dess argument utvärderar SUMPRODUCT varje matriselement mot villkoret och returnerar ett booleskt värde (SANT eller FALSK). För beräkningsändamål tilldelar Excel ett värde på 1 för de arrayelement som är SANT (uppfyller villkoret) och ett värde på 0 för arrayelement som är FALSE (uppfyller inte villkoret). Till exempel siffran 40:

• är SANT för det första villkoret så ett värde på 1 tilldelas i den första matrisen;
• är SANT för det andra villkoret så ett värde på 1 tilldelas i den andra matrisen.

Siffran 15:

• är FALSKT för det första villkoret så ett värde på 0 tilldelas i den första matrisen;
• är SANT för det andra villkoret så ett värde på 1 tilldelas i den andra matrisen.

Motsvarande nollor och nollor i varje array multipliceras tillsammans:

• För talet 40 – har vi 1 x 1 som returnerar värdet 1;
• För siffran 15 – vi har 0 x 1 som returnerar värdet 0.

Multiplicera Ones och Zeros med Range

Dessa och nollor multipliceras sedan med siffrorna i området A2: B6.

Detta görs för att ge oss de siffror som kommer att summeras av funktionen. Detta fungerar för att:

• 1 gånger vilket nummer som helst är lika med det ursprungliga numret
• 0 gånger vilket tal som helst är lika med 0

Så vi slutar med:

• 1 * 40 = 40
  0 * 15 = 0
  0 * 22 = 0
  1 * 45 = 45
  1 * 50 = 50
  1 * 55 = 55
  0 * 25 = 0
  0 * 75 = 0
  1 * 60 = 60
  0 * 100 = 0

Sammanfattar resultaten

SUMPRODUCT summerar sedan resultaten ovan för att hitta svaret. 40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250