Op Sheet1 staan de namen van verkopers in Kolom C en in Kolom D staan bedragen. In Kolom C kan de naam van dezelfde medewerker meerdere keren voorkomen.
Op Sheet2 willen we de verkoper met het hoogste totaalbedrag weergeven en vervolgens de tweede verkoper etc. Een soort van Top 10 zeg maar. Bovendien geldt een criterium, we tellen alleen de bedragen waarvan in kolom E “Sold” staat.
Sheet1

Sheet2

De formules moet je invoeren met Ctrl+Shift+Enter (niet alleen Enter)
A2
=LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23=””);MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;”Sold”);MIN(A1;SUM(IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23=””);MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);1))))
A3
=IFERROR(SUM(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23=””);MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23))>=A2;1));0)
A5
=IF($B5=””;””;INDEX(Sheet1!$C$2:$C$23;SMALL(IF(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23=””);MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;”Sold”)=$B5;ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);COUNTIFS($B$5:B5;B5))))
Invoeren met Ctrl+Shift+Enter (niet alleen Enter) en doorvoeren naar beneden
B5
=IF(ROWS($B$5:B5)>$A$3;””;LARGE(SUMIFS(Sheet1!$D$2:$D$23;Sheet1!$C$2:$C$23;IF(FREQUENCY(IF(1-(Sheet1!$C$2:$C$23=””);MATCH(Sheet1!$C$2:$C$23;Sheet1!$C$2:$C$23;0));ROW(Sheet1!$C$2:$C$23)-ROW(Sheet1!$C$2)+1);Sheet1!$C$2:$C$23);Sheet1!$E$2:$E$23;”Sold”);ROWS($B$5:B5)))
Invoeren met Ctrl+Shift+Enter (niet alleen Enter) en doorvoeren naar beneden