.. 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$61)*(x!$ C$2:$C$6<=3))
Sums (of TotalAmount) by Length of Service
=SUMPRODUCT((x!$A$2:$A$6=$A3)*(x!$C$2:$C$61)*(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" wrote:
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.