fbpx
Search
Close this search box.

How to find sum of top 3 values based on filtered criteria

Share

Facebook
Twitter
LinkedIn

Sum of top 3 values meeting filtered criteria - how to calculate using Excel formulas?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?

This article is inspired from a question asked by acpt22 in our forums.

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.

Watch this video on our YouTube channel.

Download Example Workbook

Click here to download example file and play with it. Examine the formulas & pivot table settings to learn this technique better.

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.

Learn more

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

28 Responses to “How to find sum of top 3 values based on filtered criteria ”

  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 !

  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")

  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. […]

Leave a Reply