Combine Large and Sumif - Array formula

[London78000]

Hi Everyone

I am looking for a formula to get the largest revenue by project. The issue is that I cannot not use a “simple” large and if array formula as the project appears in multiple rows. I need to get the sum by project and show the largest one. I have provided below an exemple where I need to show the top 5 projects for 2018.




Project Name - Year - revenue
Project A - 2018 - 100€
Project A - 2017 - 100€
Project A - 2018 - 200€
Project B - 2018 - 500€
Project C - 2018 - 400€
Project C - 2017 - 400€


Top 3 result in 2018

Project B - 500€
Project C - 400€
Project A - 300€

Thanks guys

[AlKey]

Enter array formula in E1 and copy down

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Please Login or Register  to view this content.

then enter in F1 and copy down

Please Login or Register  to view this content.

v	A	B	C	D	E	F
1	Project A	2018	100 €		Project B	500
2	Project A	2017	100 €		Project C	400
3	Project A	2018	200 €		Project A	300
4	Project B	2018	500 €
5	Project C	2018	400 €
6	Project C	2017	400 €

You can also use this regular formula

Please Login or Register  to view this content.