How to put data into different buckets. - PLEASE HELP

[Mayur]

I have thousands of rows of data that have one column that I need tohave a
formula that counts how many within three years and puts it into the correct
three different buckets accordingly.
ex.
0-2 3-5 5-10 and so forth.
Columbus
Louisville

i need it to look through one column and when it matches look at the length
of service column and it is between 0-2 it would count as 1 and go to the
next columbus match and length of service and so forth.

After that I need to do the same thing but instead of counting i need the
formula to add up the dollars according to $0-$100, $500-$1000 ect. and sum
up the dollars per market.

PLEASE HELP ME!!!!!!!!!!!!!!!!!!!

 

[Shane Devenshire]

Hi,

Try something like this

=COUNTIF(Year,"<=2")

=SUMPRODUCT(--(Years>=3),--(Years<=5)

is an example of what you will need for a range or year.
This assumes you have a range where you have entered the number of years.
(you did not show us any of your data)

SUMIF(Dollars,"<=100")

SUMPRODUCT(--(Dollars>=500),--(Dollars<=1000),Dollars)

You left out the range >100 <500?

This will sum the dollar data for entries that are <=100

 

[Mayur]

Market TotalAmount Length of Service
Arizona 2,099.97 2
Arizona 35.14 3
Arizona 2,477.96 5
Austin 1,173.81 7
Austin 2,425.14 15


1-3 4-10 11-20 20+ # of Accts % of Total Accts
Arizona #DIV/0!
Chicago #DIV/0!

<$1001 $1001 to $9999 $10000 to $99,999 $100,000 + # of Accts % of Total
Accts Total Dollars % of Total $
Columbus 15,833
Chicago 506,144

Okay the top portion is on two tabs. The very first part is about 2000
lines. I am looking for a formula that looks up each market (ex. Arizona)
and than the length of service and put it in the buckets (1-3, 4-10, 11-20,
etc) I dont want to add the years just want a count in each bucket.

Than i need a formula that will look up each market and add the dollars
according to each market.

I hope that makes sense. This is very complicated. I really appreciate your
help.

 

[Max]

.. This is very complicated

Not really. You can frame it all up (the multi-criteria counts and sums)
quite easily using sumproduct. Just a matter of getting familiar with it.

Here's a working sample based on your data which shows how to frame up all
of the bucketing summaries that you have in mind:

http://freefilehosting.net/download/44fme
Bucketing summary.xls

Contents are 5 sheets comprising:
x = source data sheet (your sample data in cols A to C)
Counts by Length of Service buckets
Sums (of TotalAmount) by Length of Service buckets
Counts (of TotalAmount) by TotalAmount buckets
Sums (of TotalAmount) by TotalAmount buckets


An example or 2 of framing it up given below

For bucket: 1-3 (Length of Service)
--------------------------------------
Counts by Length of Service:
=SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$6>1)*(x!$C$2:$C$6<=3))

Sums (of TotalAmount) by Length of Service
=SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$6>1)*(x!$C$2:$C$6<=3),x!$B$2:$B$6)

For bucket: <$1001 (TotalAmount)
---------------------------------------
Counts (of TotalAmount) by TotalAmount buckets
=SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$B$2:$B$6<1001))

Sums (of TotalAmount) by TotalAmount buckets:
=SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$B$2:$B$6<1001),x!$B$2:$B$6)

P/s: Ensure that there are no gaps in the various buckets when you frame it
up (adapt the operators, eg: >=, <, etc accordingly to suit the lower/upper
limits for each bucket). Adapt the range to suit your actual source data
extents.

If the above helps, press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Mayur]

It didn't work because it doesn't lookup through the 2000 rows to find the
market. I get a value# error. Do you want to see my file?

Thank you so much for helping me.

 

[Max]

Mayur,

In adapting to suit, you must of course, extend the rows range in the
formulas to cover your actuals. And change the cols and sheetnames
accordingly to suit as well. Otherwise nothing will work.

I've sent over the solution adapted to suit in private email

Please keep all discussions within the newsgroup
And do not send any file unless it is requested for

Pl mark all responses which help answer your queries by pressing the YES
buttons (like the ones below)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik