# How to find sum of top 3 values based on filtered criteria [video]

Posted on May 17th, 2013 in Excel Howtos - 27 comments

Today, lets tackle an interesting problem.

Lets say you are looking at some data as shown aside and wondering what is the sum of budgets for top 3 projects in East region with Low priority. How would you do that with formulas?

### Sum of top 3 values based on filtered criteria

Watch below video to understand how to find sum of top 3 values using formulas & pivot tables.

### Do you calculate sum of top ‘n’ values often?

Often, I have to calculate sum of top ‘n’ values and I use SUMPRODUCT + LARGE combination. SUMPRODUCT (or simply SUM) is such a versatile formula that you could almost use it when your car breaks down on a free way.

What about you? Do you calculate sum of top ‘n’ values? Which techniques do you use? Please share using comments.

If you sum & count for your living, then you are going to love below tips.

 How to transpose a values in a row to column using formulas… [Quick tip] Excel Risk Map
 Written by Chandoo Tags: advanced excel, array formulas, downloads, large, Learn Excel, pivot tables, slicers, sumproduct, top 10 values, videos Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 27 Responses to “How to find sum of top 3 values based on filtered criteria [video]”

1. Kevin says:

How come in pivot tables you cannot calculate the median?

2. Jomili says:

I'm blocked from seeing the video. Is there a text explanation anywhere?

• PPH says:

You can do something like =SUM(large(range,1),large(range,2),large(range,3)

or =SUMIF(range,">="&LARGE(range,3))

or as an array formula =SUM(IF(range>=LARGE(range,3),range))

You could also use RANK in place of LARGE with the order descending.

Or use RANK and go in descending order.

• Jomili says:

PPH, I can see how your formulas get the largest 3 in the range, but the formula in the workbook does that within the criteria "East" and "Low". How does that formula work? I'm not familiar with "Slicer" named ranges, which it appears to use.

• PPH says:

Not really sure because I haven't touched slicers yet. But in array formulas you can nest ifs to filter for criteria.

So it'd be like:

=SUM(IF(range2="East",if(range3="Low",if(range>=LARGE(range,3),range)))

• Jomili says:

Having trouble getting this formula to work:
=SUM(IF(range2=”East”,if(range3=”Low”,if(range>=LARGE(range,3),range)))
I appears to be missing a close parentheses. When I add one to the end, and use it in Chandoo's workbook, the formula is:
=SUM(IF(projects[Region]="East",IF(projects[Project Ranking]="Low",IF(projects[Budget(USD ''000)]>=LARGE(projects[Budget(USD ''000)],3),projects[Budget(USD ''000)])))). The result is 0.

• PPH says:

Oh you know what... we need to array the large formula by those criteria. It's ridiculously complex but enter this with CTRL+SHIFT+ENTER

=SUM(IF(projects[Region]="East",IF(projects[Project Ranking]="Low",IF(projects[Budget(USD ''000)]>=LARGE(IF(projects[Region]="East",IF(projects[Project Ranking]="Low",projects[Budget(USD ''000)])),3),projects[Budget(USD ''000)]))))

You'll notice the answer is 95 and not 72, because numerically there are two that are tied as the 3rd largest values, so it's adding both. Even with Chandoo's formula you may need to define which you want, or both, or whatever.

3. Rahim Zulfiqar Ali says:

Unable to watch the Video. If it's uploaded on YOUTUBE than it's still blocked in Pakistan.

4. Vaibhav Garg says:

Top 3 is simple enough once you get a hang of it, it's the bottom 3, which causes an issue, what with zero's representing no match.

Also, similarly,consider if all values were negative, the formula fails to get top 3!

• Hui... says:

@Vaibhav
Chandoo.org is a Moderated Forum
So it doesn't show up until someone like me or Chandoo approves it
I Have deleted the second post

• NARAYAN says:

Hi Vaibhav ,

Try these :

TOP 3 : =SUM(LARGE(IF((Regions="ME")*(Project_Rankings="High")=0,-10*SIGN(Budgets),(Regions="ME")*(Project_Rankings="High"))*(Budgets),ROW(\$1:\$3)))

BOTTOM 3 : =SUM(SMALL(IF((Regions="ME")*(Project_Rankings="High")=0,10*SIGN(Budgets),(Regions="ME")*(Project_Rankings="High"))*(Budgets),ROW(\$1:\$3)))

Both formulae refer to the original question for their named ranges , and criteria ; both are entered as array formulae , using CTRL SHIFT ENTER.

5. David Hager says:

How about using this array formula (works on a filtered table)?

=SUM(AGGREGATE(14,1,filtered_col,ROW(1:3)))

6. thanks for the tutorial !

• Alekya says:

7. Rizwan Akhtar says:

really knowlegeable.... Thanks Chandoo....

8. ravi datt says:

Good one

9. Ritesh says:

This is really amazing, thanks a ton Mr. Chandoo

10. Amit says:

Hi, why doesn't this formula work?

=SUMIFS(LARGE(budget_range,{1,2,3}),region_range,"East",ranking_range,"High")

• Hui... says:

@Amit
Did you enter it as an Array Formula?
Press Ctrl+Shift+Enter instead of Enter when entering the formula

• Amit says:

@Hui yes I tried that but it still does not work. Any other ideas? Thanks.

• Amit says:

Nevermind I got it!

I used =SUM(LARGE(IF(Region = "East", IF(Ranking = "High", Budget)),{1,2,3})) and entered it as an array formula

11. WL says:

to make it more flexible for TOP N:
{=SUM(LARGE((Region="East") * (Ranking="High")*(Budget), ROW(INDIRECT("1:3"))))}

Here replace 3 with top N you want to get.

12. nagesh volety says:

Thanks for the helpful tip, however I have similar problem in finding the latest date for example of first array - company1, device1, Preventive Maintenance date, How do I find the latest date into a second array, while the second array contains the company1, device1 & I need to track the latest PM Date from the first array using the similar formula - Nagesh

13. Ankit Bansal says:

Thanks for the tutorial. it was really helpful.

14. SATYAM says:

Thanks for video Chandoo,Its really helpful.But what about when we have same value like as your in example PJ001 & PJ004 have same value 22.

15. […] Calculating sum of top 3 values in a filtered list. […]

16. […] Calculating sum of top 3 values in a filtered list. […]

 How to transpose a values in a row to column using formulas… [Quick tip] Excel Risk Map