• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum of top 3 records based on filtered criteria

A

acpt22

New Member
I have been struggling to get this for many days, but could not find the exact thing I am looking for on any forum.


I have an excel table as below. I need to get the sum of top 3 budgets for every region and project ranking.


-------------------------------------------------

Region Project Ranking Budget (in USD '000)

-------------------------------------------------

ME Low 52.0

ME Low 55.0

ME Low 21.0

ME High 10.0

CE Medium 12.0

CE Low 8.0

ME High 8.0

ME Medium 22.0

ME High 15.0

CE Medium 19.0

ME Medium 8.0

ME Medium 20.0

WE High 9.0

ME Medium 13.0

ME High 16.0


I am okay to use pivot if the solution is easy. Sumifs or sumproduct formula solution should also be okay.O/p should be like this:


Please note: The totals below are just dummy numbers.


Region High Low Medium

ME 200 100 30

CE 300 100 150

WE 220 120 120


Shall appreciate a quick response.
 
Sum of ME High should be 41 (10+15+16). These are the top 3 numbers.

I am getting 49. This is adding 8 as well which it should not

Remember, I said 'Top 3'.
 
Hi acpt22,


Welcome to Chandoo_Org.


Please try below formula and let me know if its working fine for you.


1. Considering your data in column A, B and C having header. Write Low, High and Medium in cell F1, G1 and H1 respectively.


2. Write ME, CE and WE in cell E2, E3 and E4 respectively.


3. Now paste the below formula in cell F2. please press Ctrl+Shift+Enter to enter the formula as its an array formula.


=IFERROR(SUM(LARGE(IF((($A$2:$A$16=$E2)*($B$2:$B$16=F$1)),$C$2:$C$16),IF(COUNTIFS($A$2:$A$16,$E2,$B$2:$B$16,F$1)<3,COUNTIFS($A$2:$A$16,$E2,$B$2:$B$16,F$1),ROW($A$1:$A$3)))),"No match found")


4. Drag this to get your result. You can change your data range as per your need.


My formula is too long. We will wait to our excel experts to give us better formula till then u can try this.


Thanks & Regards,

Anupam Tiwari
 
Hi acpt22,


The above formula will give you sum of top 3 records as per the condition however if records matching to the condition are less than 3 that time it will give you sum of those records and if there is no record matching the conditon it will give result "No match found".


Thanks & Regards,

Anupam Tiwari
 
Hi ,


Can you try this ?


=SUM(LARGE(--(G7:G21="ME")*--(H7:H21="High")*(I7:I21),ROW($1:$3)))


Enter this as an array formula , using CTRL SHIFT ENTER.


G7:G21 is the range containing the REGION code , H7:H21 is the range containing the PROJECT RANKING , and I7:I21 is the range containing the BUDGET amounts.


Narayan
 
Hi,


So what similar to Narayan's solution, Assuming your data in A1:C16 with Header row, try this:


Code: 
=SUM(LARGE(IF($A$2:$A$16="ME",$C$2:$C$16),ROW(A1:A3)))


Press Ctrl+Shift+Enter to execute the formula.


Regards,
 
Hi acpt22,


I have corrected my above provided formula as erlier one was giving me error at one point. Below formula is now ok.


=IFERROR(SUM(LARGE(IF((($A$2:$A$16=$E2)*($B$2:$B$16=F$1)),$C$2:$C$16),IF(COUNTIFS($A$2:$A$16,$E2,$B$2:$B$16,F$1)<3,ROW(INDIRECT("A1:A"&COUNTIFS($A$2:$A$16,$E2,$B$2:$B$16,F$1))),ROW($A$1:$A$3)))),"No match found")


Use it as I mentioned above. Press Ctrl+Shift+Enter to execute the formula.


Solution provided by NARAYANK991 and Faseeh are more robust, short and sweet so we should go with their formula.


Thanks to both NARAYANK991 and Faseeh, I learnt a new formula now from your posts. Thanks to acpt22 as well.


Thanks & Regards,

Anupam Tiwari
 
Thank you NARAYANK991. Your solution worked.


The solution is:

=SUM(LARGE(--(G7:G21="ME")*--(H7:H21="High")*(I7:I21),ROW($1:$3)))
 
You don't need the
Code: 
-- to coerce the boolean arrays because you're also multiplying them. So these formulae all do the same thing:

[pre][code]{=SUM(LARGE((G7:G21="ME")*(H7:H21="High")*(I7:I21),{1,2,3}))}
[pre]=SUMPRODUCT(LARGE((G7:G21="ME")*(H7:H21="High")*(I7:I21),{1,2,3}))
[/pre]
{=SUM(LARGE(IF(G7:G21="ME",IF(H7:H21="High",I7:I21)),{1,2,3}))}[/code][/pre]
 
@acpt22 and all: I loved this question. So I made a video tutorial and article out of it here:


http://chandoo.org/wp/2013/05/17/sum-of-top-3-values-meeting-criteria/


You can download example workbook, watch solution video (that shows how to write formulas or set up pivot table) too.
 
Hi,


Thanks for writing!


May be this formula is already posted.


=SUMPRODUCT((LARGE((A1:A15=E2)*(B1:B15=F1)*(C1:C15),ROW(INDIRECT("1:3")))))


Hope this works.


Thanks/Raj
 
You must log in or register to reply here.
Back
Top