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!

 

[Roger Govier]

Hi

You could use an array formula. Commit with Ctrl+Shift+Enter when
entering for the first time, or modifying.
Do not type the curly braces { } Excel will enter these for you when
you use Ctrl+Shift+Enter

Using a cell at the top of the column of data to determine the number of
values to be taken. In my example, cell A1 would hold either 3 or 4

{=SUM(LARGE(A2:A1000,ROW(INDIRECT("1:"&A1))))}

 

[C-Man23]

Hi

You could use an array formula. Commit with Ctrl+Shift+Enter when
entering for the first time, or modifying.
Do not type the curly braces { } Excel will enter these for you when
you use Ctrl+Shift+Enter

Using a cell at the top of the column of data to determine the number of
values to be taken. In my example, cell A1 would hold either 3 or 4

{=SUM(LARGE(A2:A1000,ROW(INDIRECT("1:"&A1))))}

--
Regards

Roger Govier


"C-Man23" <[email protected]> wrote in message

Thanks Roger, You are an Angel! This works perfectly!

 

[Roger Govier]

Hi

Thanks for the feedback. Glad it all worked for you.