How to combine LARGE and SUMIF- Array formula
Requirement:
The user is looking for a formula to get the largest revenue by project. The issue is that the user cannot not use a “simple” large and if array formula as the project appears in multiple rows. The user needs to get the sum by project and show the largest one. The user has provided below an example where the user sneed 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€
Solution:
You would need a helper column at D2 with formula:
=SUMIFS($C$2:$C$7,$A$2:$A$7,A2,$B$2:$B$7,B2)
copied down
then top 3 would use Array* formula in say F2:
=INDEX($A$2:$A$7,MIN(IF(LARGE(IF($B$2:$B$7=2018,$D$2:$D$7),ROWS(F$2:F2))=$D$2:$D$7,ROW($A$2:$A$7)-ROW($A$2)+1)))
copied down 3 rows.
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to find the least negative value in an array |
| How to use an array formula to omit data if criteria met |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
