sum top x values according to reference cell

**tamskinner** · 02-15-2012, 05:31 AM

Hi please help. By reading these board I have found how to sum the top (X) values in a range, for instance using the following:
(SUM(LARGE(C5:G5,{1,2,3}))
This sums the top 3 values in the range c5:g5.
If I want to change the numbers of values to say the top 4 values or 2 values i can edit the formula accordingly. However, can I create a reference cell (say, B1) where i enter the number of values i want to total, and the formula would auto calculate?

**zbor** · 02-15-2012, 05:36 AM

Here, try this:

=SUMPRODUCT(LARGE(C5:G5,ROW(A1:INDEX(A:A,B1,))))

**tamskinner** · 02-15-2012, 05:49 AM

wow!!! thanks for very speed reply, which works! Couple of questions tho
I need to copy this down around 10 rows, so have fixed the A1:index A:A, B1).It seems to work ok, but I dont know what the Row A1:indexA:A means!!! obviously b1 is the ref cell (which in my sheet is actually h4
Many many thanks, very impressive!!

**zbor** · 02-15-2012, 05:57 AM

If you moving it around then use $:

=SUMPRODUCT(LARGE(C5:G5,ROW($A$1:INDEX($A:$A,$B$1,))))

Instead of B1 you can chose to whatever you want (only use $ around: $H$1).
Also range you can change (C5:G5).

But the rest don't change.

ROW(A1:A7) will give you array of first 7 numbers (1,2,3,4,5,6,7)
ROW(A1:A100) will give you array of first 100 numbers.

Since you don't know how many you'll use (it's defined by B1) then you need INDEX formula to determine second value.

Therefore if B1=100 INDEX(A:A, B1) will return A100
so ROW($A$1:INDEX($A:$A,$B$1,)))) will return you 1,2,3,4,5....,97,98,99,100
And that's exactly you used in your first formula {1,2,3} only for variable lenght.

(note: you can also try to use SUM(LARGE(C5:G5,ROW(A1:A3))) in your formula)

So.. When you moving this formula around you always need to be referencing to row number of A1 to An no matter what range you use in calculation.
To be more precise you can use any column but row from 1 to n (i.e. SUM(LARGE(C5:G5,ROW(M1:M3)))