Somcellen die voldoen aan meerdere criteria met Excel SUMPRODUCT

Somcellen die tussen twee waarden vallen

De functie SOMPRODUCT in Excel is een zeer veelzijdige functie die verschillende resultaten geeft, afhankelijk van de manier waarop de argumenten van de functie worden ingevoerd. Normaal gesproken, zoals de naam al doet vermoeden, vermenigvuldigt SUMPRODUCT de elementen van een of meer arrays om hun product te krijgen en voegt het vervolgens de producten bij elkaar of telt ze op. Door de syntaxis van de functie aan te passen, kan deze echter worden gebruikt om alleen de gegevens in cellen op te tellen die aan specifieke criteria voldoen. Sinds Excel 2007 bevat het programma twee functies – SUMIF en SUMIFS – die gegevens optellen in cellen die aan een of meer vastgestelde criteria voldoen. Soms is SUMPRODUCT echter gemakkelijker om mee te werken als het gaat om het vinden van meerdere aandoeningen die betrekking hebben op hetzelfde bereik, zoals wordt weergegeven in de bovenstaande afbeelding.

SOMPRODUCT Functiesyntaxis naar somcellen

De syntaxis die wordt gebruikt om SOMPRODUCT gegevens te laten optellen in cellen die aan specifieke voorwaarden voldoen, is: = SOMPRODUCT([condition1] * [condition2] * [array]) condition1, condition2 – de voorwaarden waaraan moet worden voldaan voordat de functie het product van de array vindt. array – een aaneengesloten reeks cellen

Voorbeeld: gegevens optellen in cellen die aan meerdere voorwaarden voldoen

Het voorbeeld in de bovenstaande afbeelding voegt de gegevens toe in cellen in het bereik D1 tot E6 die tussen 25 en 75 liggen.

De SOMPRODUCT-functie invoeren

Omdat in dit voorbeeld een onregelmatige vorm van de functie SOMPRODUCT wordt gebruikt, kan het dialoogvenster van de functie niet worden gebruikt om de functie en zijn argumenten in te voeren. In plaats daarvan moet de functie handmatig in een werkbladcel worden getypt.

1. Klik op cel B7 in het werkblad om het de actieve cel te maken;
2. Voer de volgende formule in cel B7 in:=SOMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))
3. Het antwoord 250 zou moeten verschijnen in cel B7
4. Het antwoord werd verkregen door de vijf getallen in het bereik (40, 45, 50, 55 en 60) op te tellen die tussen 25 en 75 liggen. Het totaal is 250

De SOMPRODUCT-formule doorbreken

Als er voorwaarden worden gebruikt voor zijn argumenten, evalueert SOMPRODUCT elk array-element tegen de voorwaarde en retourneert een Booleaanse waarde (TRUE of FALSE). Voor berekeningen kent Excel een waarde toe van 1 voor die array-elementen die WAAR zijn (voldoen aan de voorwaarde) en een waarde van 0 voor array-elementen die ONWAAR zijn (voldoen niet aan de voorwaarde). Bijvoorbeeld het getal 40:

• is WAAR voor de eerste voorwaarde, dus een waarde van 1 wordt toegewezen in de eerste array;
• is WAAR voor de tweede voorwaarde, dus een waarde van 1 wordt toegewezen in de tweede array.

Het getal 15:

• is ONWAAR voor de eerste voorwaarde, dus een waarde van 0 wordt toegewezen in de eerste array;
• is WAAR voor de tweede voorwaarde, dus een waarde van 1 wordt toegewezen in de tweede array.

De corresponderende enen en nullen in elke array worden met elkaar vermenigvuldigd:

• Voor het getal 40 – hebben we 1 x 1 die een waarde van 1 retourneert;
• Voor het getal 15 – hebben we 0 x 1 die een waarde van 0 retourneert.

De enen en nullen vermenigvuldigen met het bereik

Deze enen en nullen worden vervolgens vermenigvuldigd met de getallen in het bereik A2 : B6.

Dit wordt gedaan om ons de getallen te geven die door de functie worden opgeteld. Dit werkt omdat:

• 1 keer een willekeurig getal is gelijk aan het oorspronkelijke getal
• 0 keer een willekeurig getal is gelijk aan 0

Dus we eindigen met:

• 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

De resultaten optellen

SOMPRODUCT somt vervolgens de bovenstaande resultaten op om het antwoord te vinden. 40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250