Indien je de bedragen in Kolom F (Amount) wil optellen gebaseerd op de 5 criteria in kolommen A:E (First – Last – Company – Year – Month), heb je wat formules nodig. Vanwege het overzicht zijn de gegevens al gegroepeerd weergegeven. Bekijk bijvoorbeeld de gegevens in Rij 2 en 3. Die zijn hetzelfde namelijk:

Andrew Fuller Tokyo Traders 2015 11
Andrew Fuller Tokyo Traders 2015 11

De twee bedragen bij elkaar opgeteld € 15,67 + € 6,19 = € 21,86
En dat record zie je staan in Rij 2 in de Kolommen H:M

Stel je voor dat de records in Kolommen A:F door elkaar staan en dat het om honderden records gaat, je kunt je dan voorstellen dat het een hele klus is om eerst alles te sorteren en vervolgens de bedragen die bij de passende records horen op te tellen. Door enkele formules in de kolommen H:M te plaatsen.

Samengevat: Tel de bedragen in Kolom F op voor elke unieke combinatie in de Rijen  A:E.

Dan nu de formules. Je moet natuurlijk eerst gegevens hebben zoals hierboven. Vervolgens maak je een paar benoemde bereiken. Doe dat als volgt:

– Ga met de cursor in je tabel staan.
– Druk op Ctrl+Shift+F3 Je komt bij: Create names from selection.
– Check > Top row
– En dan OK.

Je hebt nu 5 benoemde bereiken namelijk:

First =Sheet2!$A$2:$A$45
Last =Sheet2!$B$2:$B$45
Company =Sheet2!$C$2:$C$45
Year =Sheet2!$D$2:$D$45
Month =Sheet2!$E$2:$E$45

Let op dat de formule naar Sheet2! verwijst.

Vervolgens, ga naar Formulas > Name manager > New. Vul in:
Name: RowVector
Refers to: =ROW(First)-ROW(INDEX(First;1;1))+1

Onderstaande formules invoeren met Ctrl+Shift+Enter

H2 =IFERROR(INDEX(First;SMALL(IF(FREQUENCY(IF(First<>"";MATCH(First&"|"&Last&"|"&Company&"|"&Year&"|"
&Month;First&"|"&Last&"|"&Company&"|"&Year&"|"&Month;0));RowVector);RowVector);ROWS(H$2:H2)));"")

I2 =IFERROR(INDEX(Last;SMALL(IF(FREQUENCY(IF(First<>"";MATCH(First&"|"&Last&"|"&Company&"|"&Year&"|"
&Month;First&"|"&Last&"|"&Company&"|"&Year&"|"&Month;0));RowVector);RowVector);ROWS(I$2:I2)));"")

J2 =IFERROR(INDEX(Company;SMALL(IF(FREQUENCY(IF(First<>"";MATCH(First&"|"&Last&"|"&Company&"|"&Year&"|"
&Month;First&"|"&Last&"|"&Company&"|"&Year&"|"&Month;0));RowVector);RowVector);ROWS(J$2:J2)));"")

K2 =IFERROR(INDEX(Year;SMALL(IF(FREQUENCY(IF(First<>"";MATCH(First&"|"&Last&"|"&Company&"|"&Year&"|"
&Month;First&"|"&Last&"|"&Company&"|"&Year&"|"&Month;0));RowVector);RowVector);ROWS(K$2:K2)));"")

L2 =IFERROR(INDEX(Month;SMALL(IF(FREQUENCY(IF(First<>"";MATCH(First&"|"&Last&"|"&Company&"|"&Year&"|"
&Month;First&"|"&Last&"|"&Company&"|"&Year&"|"&Month;0));RowVector);RowVector);ROWS(L$2:L2)));"")

Invoegen met alleen Enter

M2 =IF($H2="";"";SUMIFS(Amount;First;$H2;Last;$I2;Company;$J2;Year;$K2;Month;$L2))

Alle formules tenslotte doorvoeren naar beneden.