# Thread: Sum 3 largest count values (Excel 2000>)

1. ## Sum 3 largest count values (Excel 2000>)

Is it possible to do this?

In the attached, I can sum the 3 largest values... 6+6+5..., but I actually want to sum 6+5+4 (3 largest "count" values).
Any ideas?

2. ## Re: Sum 3 largest count values (Excel 2000>)

From your example, I assume that you mean the three largest unique values.
See the attached workbook. The largets value is calculated by a simple MAX formula, the second and third largest by an array formula.

3. ## Re: Sum 3 largest count values (Excel 2000>)

You assume correctly...
This is good...many tx! I cant believe how knowledgable you are in these things!!!

PS: Is it possible to design this into ONE nested function. I always prefer nest functions above aditional columns of intermediate functions, which I have to hide!

4. ## Re: Sum 3 largest count values (Excel 2000>)

Here you go. Try and figure it out! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
<code>
=SUM(LARGE(IF(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-MIN(ROW(A2:A11))+1,A2:A11,""),{1,2,3}))
</code>
You must enter it as an array formula, i.e. confirm with Ctrl+Shift+Enter.

5. ## Re: Sum 3 largest count values (Excel 2000>)

<img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

I'll cheat and use the Evaluate formula dialog!!

PS: This is a four nest function? Correct!

6. ## Re: Sum 3 largest count values (Excel 2000>)

Yes:
<pre>=SUM(LARGE(IF(MATCH(A2:A11,A2:A11,0)=ROW(A2:A 11)-MIN(ROW(A2:A11))+1,A2:A11,""),{1,2,3}))
'0 1 2 3 3 3 4
</pre>

The second ROW function is 4 levels deep counting from the top-level SUM function.

7. ## Re: Sum 3 largest count values (Excel 2000>)

<img src=/S/joy.gif border=0 alt=joy width=23 height=23>
I'm getting there....

#### Posting Permissions

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