Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highe

[C-Man23]

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9:C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

 

[Guest]

Hi
You might want to use rank(no,range) this will tell you the rank of the
number.
You can then use a sumif to just add up the top x numbers.

You would need to incorporate a check as if two numbers are the same they
would have the same ranking, but at least you would know which ones they were
and could check for them easily

Paul