COUNT (of COUNTA) wordt gebruikt om te tellen, SUM om op te tellen als er geen voorwaarde van toepassing is.
COUNTIF wordt gebruikt om te tellen, SUMIF om te sommeren als er sprake is van één enkele voorwaarde.
SUMPRODUCT of een matrixformule wordt gebruikt voor zowel tellen als optellen wanneer er twee of meer voorwaarden van toepassing zijn. Ik noem deze respectievelijk multivoorwaardelijke telling en multivoorwaardelijke som.
De syntaxis van COUNTIF:
COUNTIF(Bereik;Voorwaarde)
waarbij Voorwaarde wordt opgebouwd met vergelijkingsoperatoren en een constante (in een cel) of een berekening.
Voorbeelden:
=COUNTIF(A1:A10;”>”&B1)
telt de waarden in A1:A10 groter dan de waarde in B1;
=COUNTIF(A1:A10;”>” & AVERAGE(A1:A10))
telt de invoeren in A1:A10 groter dan het gemiddelde van A1:A10 (een berekening).
De syntaxis van SUMIF:
SUMIF(Bereik1;Voorwaarde;Bereik2)
Telt de cellen in Bereik2 op die gekoppeld zijn aan (in dezelfde rij als) de cellen van Bereik1 waarvoor de voorwaarde geldt. Merk op dat Bereik2 hetzelfde kan zijn als Bereik1.
Voorbeelden:
=SUMIF(A1:A10;D1;B1:B10)
telt elke invoer in B1:B10 op als de bijbehorende invoer in A1:A10 gelijk is aan de waarde in D1;
=SUMIF(E1:E10;”>” & AVERAGE(E1:E10);E1:E10)
die kan worden ingekort tot
=SUMIF(E1:E10;”>” & AVERAGE(E1:E10))
Tel alle getallen in E1:E10 op en bepaal of deze groter zijn dan het gemiddelde van E1:E10.
Wat doet SUMPRODUCT?
- Vermenigvuldigt als eerste de corresponderende items in de matrix (Array) en telt de uitkomsten vervolgens op.
Syntax:
SUMPRODUCT(array1;array2;array3; …)
- Array1, array2, array3, … zijn 2 to 30 arrays (matrices) waarvan de componenten vermenigvuldigt worden en dan worden opgeteld.
- De array (Matrix) argumenten moeten dezelfde dimensies/grootte/omvang/afmeting hebben. Wordt aan die voorwaarde niet voldaan geeft SUMPRODUCT de #VALUE! foutmelding.
- SUMPRODUCT behandelt array items die NIET numeriek zijn als waren ze 0 (nul).
Example
=SUMPRODUCT(A1:B3;D1:E3)
De formule lijkt aan te geven dat we eerst optellen, maar nee, we gaan eerst vermenigvuldigen en de resultaten daarna optellen:
32 + 47 + 86 + 67 + 15 + 93.
Als je bovenstaande formule hebt ingevoerd kun je A1:B3 selecteren en dan op F9 drukken. Vervolgens D1:E3 selecteren en weer op F9 drukken.
=SUMPRODUCT({3\4;8\6;1\9};{2\7;6\7;5\3}) is gelijk aan 156
Opmerking
Het voorgaande voorbeeld geeft hetzelfde resultaat als de formule SUM(A1:B3*D1:E3) die als Array (matrix) is ingevoerd d.m.v. Ctrl+Shift+Enter. Het gebruik van matrices biedt een algemenere oplossing voor bewerkingen die vergelijkbaar zijn met SUMPRODUCT. U kunt bijvoorbeeld de som van de kwadraten van de elementen in A1:B3 berekenen met de formule SUM(A1:B3^2) die als matrix is ingevoerd.
Een ander voorbeeld.
Stel dat A1:E24 de voorbeeldgegevens van de maandelijkse verkoop (voorbeeld afkomstig van Walkenbach) bevat in een werkblad met de naam Data:
={“Month”\”Sales Rep”\”Region”\”Contacts”\ “Sales”;”Jan”\”Bob”\”North”\58\283800; “Jan”\”Frank”\”North”\35\507200; “Jan”\”Paul”\”South”\25\107600; “Jan”\”Randy”\”South”\47\391600; “Jan”\”Mary”\”South”\39\226700; “Feb”\”Bob”\”North”\44\558400; “Feb”\”Jill”\”North”\46\350400; “Feb”\”Frank”\”North”\74\411800; “Feb”\”Paul”\”South”\29\154200; “Feb”\”Randy”\”South”\45\258000; “Feb”\”Mary”\”South”\52\233800; “Mar”\”Bob”\”North”\30\353100; “Mar”\”Jill”\”North”\44\532100; “Mar”\”Frank”\”North”\57\258400; “Mar”\”Paul”\”South”\13\286000; “Mar”\”Randy”\”South”\14\162200; “Mar”\”Mary”\”South”\36\134300; “Apr”\”Bob”\”North”\54\595500; “Apr”\”Jill”\”North”\44\480100; “Apr”\”Frank”\”North”\79\555500; “Apr”\”Paul”\”South”\36\328200; “Apr”\”Randy”\”South”\31\154200; “Apr”\”Mary”\”South”\22\200600}
Opmerking 1. Kopieer dit inclusief het =-teken. Activeer A1 in een werkblad dat u Data noemt, plak het in de formulebalk en druk op Enter. Activeer A1, selecteer het bereik A1:E24, ga naar de formulebalk en voer in met CtrlL+Shift+Enter. Kopieer het bereik A1:E24, activeer vervolgens alleen A1 en voer Bewerken|Plakken speciaal -> Waarden uit.
Merk op dat in de maand januari de verkoper Bob 58 klanten (contacten) had in de regio Noord, waarmee hij een omzet van $ 283.800 behaalde. Ook zijn er elke maand verschillende verkopers actief in een regio.
Opmerking 2. Geef de volgende namen aan de respectieve bereiken via het naamvak of via Invoegen|Naam|Definieer:
MONTHS for A2:A24
REPS for B2:B24
REGIONS for C2:C24
CONTACTS for D2:D24
SALES for E2:E24
Stel dat we een overzicht willen van contacten en verkopen per verkoper. SUMIF is hiervoor uitermate geschikt. De voorwaarde voor het berekenen van het totaal (som) is de naam van de verkoper. Om dit overzicht te verkrijgen, maak je in een werkblad (dat u SUMIF kunt noemen) het volgende in B4:B12:
={“Sales Rep”;”Bob”;”Frank”;”Paul”;”Randy”;”Mary”;”Jill”;”Ron”;”Tim”}
en voer in met CtrlL+Shift+Enter. Dit is een unieke lijst met de namen van kolom B van de Sheet ‘Data’.
en de volgende lijst met labels in C4:D4
={“Contacts”\”Sales”}
In C5 enter: =SUMIF(REPS;B5;CONTACTS)
In D5 enter: =SUMIF(REPS;B5;SALES)
Selecteer C5:D5 en kopieer naar beneden. Je krijgt dan:
={“Sales Rep”;”Contacts”;”Sales”\ “Bob”;560;4403700\ “Frank”;644;4373400\ “Paul”;392;2992300\ “Randy”;380;3394000\ “Mary”;476;2730300\ “Jill”;548;4153900\ “Ron”;51;297244\ “Tim”;73;400305}
Terug naar SUMPRODUCT.
Maak in nog een ander werkblad (dat je SUMPRODUCT kunt noemen) de volgende lijst met maanden in B10:B21:
={“Jan”; “Feb”; “Mar”; “Apr”; “May”; “Jun”; “Jul”; “Aug”; “Sep”; “Oct”; “Nov”; “Dec”}
De volgende lijst met labels in C9:E9:
{“Sales Reps”\”Contacts”\”Sales”}
in B1 het volgende label: Regio.
Het laatste blad is bedoeld om een overzicht per regio per maand te maken.
Als we “North” invoeren in B2, zal het overzicht de regio “North” omvatten. Als we niets invoeren in B2 (we laten het leeg), zal het overzicht alle regio’s samen omvatten. Nu de SUMPRODUCT-formules:
Voer in C10 in en kopieer naar beneden tot en met de maand december (C21):
=IF(LEN($B$2);SUMPRODUCT((MONTHS=B10)*(REGIONS=$B$2));
COUNTIF(MONTHS;B10))
Als B2 ‘North’ is, wordt het SUMPRODUCT-gedeelte van deze IF-formule uitgevoerd, wat resulteert in een telling die aangeeft dat er 2 verkopers actief zijn geweest in de maand ‘jan’ in de regio ‘North’.
Het SUMPRODUCT-gedeelte bestaat hier uit twee booleaanse termen: (MONTHS=B10) en (REGIONS=$B$2) die met AND worden gecombineerd, dat wil zeggen * tussen deze termen is een booleaanse AND die vergelijkbaar is met de logische functie AND [ Opmerking: AND(MONTHS=B10,REGIONS=$B$2) is niet toegestaan, omdat AND in Excel geen constante array oplevert, maar slechts één enkel logisch resultaat. ]
Wat het SUMPRODUCT-gedeelte hierboven doet, is een multiconditionele telling. Een rij van een maand en een regio wordt geteld als de maandwaarde “Jan” is en als de regiowaarde “noord” is. Hoe werkt het?
Elke cel in het MONTHS-bereik wordt geëvalueerd om te zien of het “Jan” is, wat leidt tot de array:
{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
en elke cel in het REGIONS bereik wordt geëvalueerd en leidt tot de array (matrix):
{TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE}
En we weten dat TRUE altijd wordt gedefinieerd als 1 en FALSE wordt gedefinieerd als 0. Zodat we krijgen
=IF(LEN($B$2);SUMPRODUCT({1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}*{1;1;0;0;0;1;1;1;0;0;0;1;1;1;0;0;0;1;1;1;0;0;0});COUNTIF(MONTHS;B10))
Door deze twee arrays te vermenigvuldigen ontstaat de volgende array:
{1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Waarbij TRUETRUE = 1, TRUEFALSE = 0 en FALSE*FALSE = 0 [dat wil zeggen dat Excel TRUE omzet naar 1, FALSE naar 0 en de vermenigvuldiging uitvoert]
Vervolgens wordt de resulterende reeks van 1-en en 0-en opgeteld, wat 2 oplevert, het aantal dat we zoeken.
Voer in D10 in en kopieer naar beneden tot en met de maand december:
=IF(LEN($B$2);SUMPRODUCT((MONTHS=B10)*(REGIONS=$B$2);(CONTACTS));SUMIF(MONTHS;B10;CONTACTS))
Het SUMPRODUCT-gedeelte van de voorgaande IF-formule produceert een totaal van alle klanten/contacten per regio en per maand, wat een multiconditionele som is. Dat wil zeggen dat het alle waarden in CONTACTS optelt als de bijbehorende MONTHS-waarden “Jan” zijn en de REGIONS-waarden “north”.
Als B2 ‘North’ is, wordt het SUMPRODUCT-gedeelte uitgevoerd, wat resulteert in een totaal (som) dat aangeeft dat er in de maand ‘jan’ in de regio ‘North’ 93 klanten/contacten zijn bediend/gefactureerd.
We hebben de resultaatmatrix van (MONTHS=B10)*(REGIONS=$B$2) al gezien:
=IF(LEN($B$2);SUMPRODUCT({1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};{58;35;25;47;39;44;46;74;29;45;52;30;44;57;13;14;36;54;44;79;36;31;22});SUMIF(MONTHS;B10;CONTACTS))
Resulteert in:
{1; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Vervolgens wordt vermenigvuldigd met:
{58; 35; 25; 47; 39; 44; 46; 74; 29; 45; 52; 30; 44; 57; 13; 14; 36; 54; 44; 79; 36; 31; 22}
bestaande uit waarden in CONTACTS, krijgen we de array (zie de MS-uitleg aan het begin):
{58; 35; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Die uiteindelijk wordt opgeteld, wat resulteert in 93.
Wanneer je het numerieke gedeelte van een reeks tekenreeksen wilt verwijderen, zoals
={“vbn52”;
“vb263”;
“s45”}
Gebruik de formule:
=SUBSTITUTE(A1; RIGHT(A1; SUMPRODUCT((LEN(A1) – LEN(SUBSTITUTE(A1; {0\1\2\3\4\5\6\7\8\9};””)))));””)
Resultaat:
vbn, vb, s
=SUBSTITUTE(A1; RIGHT(A1; SUMPRODUCT((LEN(A1) – LEN(SUBSTITUTE(A1; {0\1\2\3\4\5\6\7\8\9};””)))));””)
Sommige mensen waren verbaasd over de werking van de laatste formule.
Het resultaat van de SUBSTITUTE-matrix is een argument voor de functie LEN, en dat is op zijn beurt onderdeel van een matrixexpressie die een argument is voor SUMPRODUCT. SUMPRODUCT reduceert dit vervolgens tot één getal. Als [A1] ‘VBN1234567890’ bevat, werkt de formule als volgt.
SUBSTITUTE(A1;{0,1,2,3,4,5,6,7,8,9};””) retourneert de matrix
{“VBN123456789”, “VBN234567890”, “VBN134567890”, “VBN124567890”, “VBN123567890”, “VBN123467890”, “VBN123457890”, “VBN123456890”, “VBN123456790”, “VBN123456780”}
LEN(SUBSTITUTE(A1; {0,1,2,3,4,5,6,7,8,9};””)) retourneert de matrix
{12, 12, 12, 12, 12, 12, 12, 12, 12, 12}
(LEN(A1) – LEN(SUBSTITUTE(A1; {0,1,2,3,4,5,6,7,8,9};””))) retourneert de matrix
{1,1,1,1,1,1,1,1,1,1}
SUMPRODUCT((LEN(A1) – LEN(SUBSTITUTE(A1; {0,1,2,3,4,5,6,7,8,9};””)))) retourneert
10
Full credits go to Aladin Akyurek, member of the mrexcel.com board