Hi Joel,

I really appreciate your help, however this will give me a running total on
the new column where for example the first 100 records will add up to 100K,
and then i would have to start the same running total to get the next bucket.

Is there away that i can divide the whole 250 records into 4 buckets by
pressing a button or running a macro which would base the division on the
result of dcount and dsum. since i will be doing this in the future with much
larger file and more than 100 buckets

"Joel" wrote:

Your results won't be exact but close to the results you want.

1) Sort the records by amount
2) In a new column on the 1st record that is greater or equal to $500.00 put
this formula
=sum(C$100:C100)

Notice the dollar sign is on the first 100 only where the row number is the
1st row = 500.

3) copy formula down the worksheet.

4) Your buckets can be extract base on the new column amount

bucket 1 : the totals 0 to $100,000
bucket 2 : the totals 100,000 to $200,000
bucket 3 : the totals 200,000 to $500,000
bucket 4 : the totals 500,000 to $1,000,000
bucket 5 : the totals 1,000,000 to $2,000,000


"David" wrote:

Hi everyone,

I have a worksheet with over 20000 records, i did a dcount based on amounts
from 500-5000 and i got a result of 250 records, and dsum based on the same
criteria (500-5000) i got 2,000,000. further i want to distribute the number
of contracts and amount into 4 buckets as follow

Dsum result 10% 10% 30% 50%
total
1,000,000 100,000 100,000 300,000 500,000 1,000,000

Dcount result 10% 10% 30% 50% total
250 25 25 75 125 250

Can i somehow exctract, copy list of contracts in each bucket that will add
up to the dsum amount distribution for each bucket?

for example: a formula or macro that will give me 25 contracts out of 20,000
when i add the amounts it should come up to 100,000 and all contracts must be
between 500-5000.

I really appreciate any help i can get on this one.