Sum top 5 values based based on flag value

**Snoopy2003** · 08-07-2012, 03:16 PM

Hello,

I use the following formula to sum top 5 values in a column:

=IF(V4>0,SUMPRODUCT((V$11:V$1007>LARGE(V$11:V$1007,6))*(V$11:V$1007)),"")

I added a flag column: "count Yes/No".

I wish to sum top 5 values where flag value is "Yes"

I tried:

=IF(V4>0,SUMPRODUCT((V$11:V$1007>LARGE(V$11:V$1007,6))*(V$11:V$1007)*(U$11:U$1007="Yes")),"")

but it does not work.

Can you help me ?

Thank you

**martindwilson** · 08-07-2012, 03:44 PM

=IF(V4>0,SUMPRODUCT((V$11:V$1007>LARGE(V$11:V$1007,6))*(V$11:V$1007)),"") does not sum large 5 in a column
=SUM(LARGE(V11:V1007,{1,2,3,4,5})) does

=SUMPRODUCT(LARGE((U11:U1007="y")*(V11:V1007),{1,2,3,4,5}))
sums the 5 largest where u=y

**Cutter** · 08-07-2012, 07:41 PM

What about:

=SUMIF(V11:V1007,">"&LARGE(V11:V1007,6))

**Snoopy2003** · 08-07-2012, 08:44 PM

Thank you for your help.

That solve my problem.

**Cutter** · 08-07-2012, 09:18 PM

If you're talking to me, you're welcome.

Please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

**martindwilson** · 08-08-2012, 02:47 AM

cutter that fails if the top 6 or more are all the same number or even if say large 5 and 6 are the same then it only sums the top 4

**Cutter** · 08-08-2012, 10:27 AM

You're right, Martin. I based it on the first formula in the first post and offered it as an alternative to SUMPRODUCT().

Still don't know which solution was being referred to in post #4.