Return with sum of top 10 cells in a range which has duplicates

[billj]

Hi everyone,

I got a formula to calculate the sum of largest 10 cells in a specified range:

Please Login or Register  to view this content.

The problem with this formula is that if there are duplicates in the range, then the number is unexpected. For example, if the largest 20 cells all have a value of 5, then the formula will sum up all these 20 cells. But actually I just want to sum up the only 10 cells, even if there are duplicates.

Can you give me a UDF which returns the sum of the top N cells, if I supply the range and N value? For example, SumTopN(Rng1,N). This UDF will always return the exactly largest N cells even if there are duplicates.

It might be interesting too if a simple Excel formula will do the job.

[JeteMc]

Try this instead:

Please Login or Register  to view this content.

Let me know if you have any questions.

[AlKey]

Try these two formulas to sum top 10 of unique values

enter as a regular formula

Please Login or Register  to view this content.

or an array formula

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

for helper column use this formula in B2 and copy down

Please Login or Register  to view this content.

	A	B	C	D
1	Values	Helper		Result
2	25	1		288
3	25	2
4	32	1
5	48	1
6	25	3
7	37	1
8	45	1
9	32	2
10	8	1
11	12	1
12	45	2
13	32	3
14	56	1
15	45	3
16	32	4
17	19	1
18	6	1
19	8	2
20	4	1

Sheet: Sheet1

[billj]

Many thanks, JeteMc and AlKey. Both of you give me very advanced Excel formulas. Thanks a lot.

Just wondering if there is a handy UDF which can also make it work. I guess for a lot of VBA gurus, this is a simple question

[billj]

I just created a UDF:

Please Login or Register  to view this content.

[JeteMc]

RE: Post #5 Congratulations. RE: Post #4 You're welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. Hope that you have a good day.