# Sum product with LARGE func with Criteria

1. ## 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?

2. ## Re: Sum product with LARGE func with Criteria

Try

G6
Formula:
`Please Login or Register  to view this content.`

3. ## 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

4. ## 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.

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

#### 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