+ Reply to Thread
Results 1 to 4 of 4

Sum product with LARGE func with Criteria

  1. #1
    Registered User
    Join Date
    01-06-2010
    Location
    sydney
    MS-Off Ver
    Excel 2007
    Posts
    17

    Sum product with LARGE func with Criteria

    Hi Gurus

    I am trying to come up with a formula which will allow me to get Column G in my attached spreadsheet.

    Column F formula gives me the the top 5 average and it corresponds to sites 11 - site 15 (Column A). I want column G to give me the Last year figures for the same sites column F picked out (sites 11-15) and give me the average for them. Is there a neat way to do this without using any helper columns?
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,882

    Re: Sum product with LARGE func with Criteria

    Try

    G6
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,647

    Re: Sum product with LARGE func with Criteria

    g6=SUMPRODUCT(LARGE((B$6:B$100)*(C$6:C$100>=LARGE(C$6:C$100,5)),{1,2,3,4,5}))/5
    I am unable post other formula here see the attached file
    Attached Files Attached Files
    Last edited by samba_ravi; 03-28-2018 at 03:50 AM.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,589

    Re: Sum product with LARGE func with Criteria

    Or try:

    =AVERAGE(IF(C6:C29>=LARGE(C6:C29,5),B6:B29))

    Enter with Ctrl+Shift+Enter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SUM IF (criteria) then PRODUCT
    By manywise in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 09-04-2014, 07:01 PM
  2. Product if 3 criteria
    By ygoksel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2014, 06:45 AM
  3. Using PRODUCT for large data set
    By readams07 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2014, 12:53 PM
  4. Replies: 10
    Last Post: 01-02-2013, 10:56 AM
  5. Sum Product with Two Criteria
    By lel33 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2012, 01:37 PM
  6. Replies: 1
    Last Post: 01-14-2012, 12:00 PM
  7. Product with Criteria
    By axed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2010, 06:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1