Creating formula for Variable Length Array

D

djm182

New Member
Joined
Apr 20, 2009
Messages
2
I am trying to create a formula to get the sum of the N smallest items in a list of data that is larger than N. I can achieve the desired result by hardcoding the array. For example, if N=6:

=SUM(SMALL(A10:Z10,{1,2,3,4,5,6}))

will give me the sum of the six smallest values in the range.

Problem is that, as more of the range gets populated over time, I need to pull more than just the six smallest items.

Logically, I thought something like this would work:

=SUM(SMALL(A10:Z10,{1:6}))

but it does not.

How can I alter the formula to employ a variable for the number of items to sum?

Thanks,
Dave
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Do you know that that's completely unnecessary?

=SUM(SMALL(A10:Z10,6)) is enough. Change 6 to anything you like.
 
Upvote 0
I think you want
Rich (BB code): 
=SUM(IF(A10:Z10<=SMALL(A1:Z10,6),A10:Z10))
entered with CTRL+SHIFT+ENTER

lenze
Edit: You can use a cell reference for the number of items to sum
Rich (BB code): 
=SUM(IF(A10:Z10<=SMALL(A1:Z10,$A$1),A10:Z10))
 
Last edited:
Upvote 0
Hi Dave,

Possibly this:
Code: 
=SUMIF(A10:Z10,"<="&SMALL(A10:Z10,6))

Hope that helps...
 
Upvote 0
Colin's formula should also work quite well and is probably more efficient!!
lenze
 
Upvote 0
We might have to change both of them if we're concerned about items having equal values taking us outside of the 6th item. Dave, eg. If the 8 lowest value items all have a value of 2, should all eight of them be summed, or only six of them?
 
Last edited:
Upvote 0
For the smallest n numbers where A1 contains n

=SUMPRODUCT(SMALL(A10:Z10,ROW(INDIRECT("1:"&A1))))

that will only sum n items however many duplicates there are.....
 
Upvote 0
Thank you all for the responses. Wow...if only the customer service were that good everywhere.

Lenze and Colin's solutions worked but as Colin suggested, the formula breaks in the event many or all of the data are equal.

Barry's solution works, regardless of the nature of the data.

Thanks again!!
 
Upvote 0
Another way (I'm not saying it's necessarily better) to create the array is to use INDEX:INDEX, like this:

Code: 
=SUMPRODUCT(SMALL(A10:Z10,ROW(INDEX(A:A,1):INDEX(A:A,6))))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
427
HighAndWilder
H
S
  • Question
Formula for Equity Vesting
Replies
6
Views
482
Dave Patton
D
A
  • Solved
Create Named Range for each column based on column heading text.
Replies
5
Views
156
Cubist
Cubist
J
  • Question
Sumproduct with different array sizes
2
Replies
19
Views
511
jkj115
J
F
  • Solved
Dynamic Array Formula Problem
Replies
5
Views
193
KRice
K

Forum statistics

Threads
1,216,122
Messages
6,128,961
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top