# Summing Highest Values based on two columns

1. ## Summing Highest Values based on two columns

Hi all,

John Smith 500 76
John Smith 500 62
Joe Green 1000 42
Joe Green 1000 76
Joe Green 1000 58
John Smith 1000 77

So what I need is a formula that for each person and category, it will take the two highest values and average them. For exam, John Smith 500 - should average 73.62, Joe Green 1000 - should average 76,58, John Smith 1000 - average should be 77

does anyone know how to do this using excel formulas

2. ## Re: Summing Highest Values based on two columns

Can you attach a sample sheet?

3. ## Re: Summing Highest Values based on two columns

I am not exactly sure how your table is structure but the sample file should give you a good start

Enter the formula with Ctrl + Shift + Enter =AVERAGE(LARGE(IF((\$A\$1:\$A\$6=F1),\$B\$1:\$B\$6),{1,2}))

4. ## Re: Summing Highest Values based on two columns

If those are in columns A1:C6, and you have unique name/category combos listed starting in D1:E1, then in F1:

=AVERAGE(LARGE(IF(\$A\$1:\$A\$6=D1,IF(\$B\$1:\$B\$6=E1,\$C\$1:\$C\$6)),{1,2}))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

5. ## Re: Summing Highest Values based on two columns

What is the significance of CTRL+SHIFT+ENTER?

6. ## Re: Summing Highest Values based on two columns

It tells Excel that you have input an Array Formula.

7. ## Re: Summing Highest Values based on two columns

I've attached a sample and on the same sheet demonstrated the values to return in the results.. I'll take a look at the suggestions. Thanks all.

8. ## Re: Summing Highest Values based on two columns

Try in K2:

=AVERAGE(LARGE(IF(\$A\$2:\$A\$13=H2,IF(\$B\$2:\$B\$13=I2,IF(\$C\$2:\$C\$13=J2,\$D\$2:\$D\$13))),{1,2}))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

Note: in column A, you have the name Brown with extra space at the end.. they need to be removed.

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