hi -
is there a way to do this...

i have a file that reports how many times we attempted to call a customer in
different markets and we need to consolidate by marker by attempt

Col A = Market (CA, IL, SC, VA)
Col B = Attempt Success Category (0 thru 9)
Col C = Actual # of Attemtps

the data looks like this...
Market Attempt Sum
CA 0 5
CA 1 1
CA 2 3
CA 4 5
CA 7 1
VA 9 1
IL 0 4
IL 3 1
IL 5 1
IL 6 1
SC 7 1
VA 8 2

and we'd like to show something like this per market.
CA
Attempt Total
0 5
1 1
2 3
3 0
4 5
5 0
6 0
7 1
8 0
9 0

i can do a sumif but i have to sort and specify the range. for isntance, for
california would use range B2:B105, for IL range B106:B300 and so on. any way
to make it so the range changes dynamiccaly based on column A that has the
market??

This is why they made pivot tables. Here's a tutorial that looks pretty
reasonable as a starting point. Your Row Labels will be first State, then
Attempt Success Category. Your data will be the number of attempts, and
you'll want to use the field settings to make them SUM.

http://www.homeandlearn.co.uk/excel2...l2007s7p7.html

"Mayte" wrote:

hi -
is there a way to do this...

i have a file that reports how many times we attempted to call a customer in
different markets and we need to consolidate by marker by attempt

Col A = Market (CA, IL, SC, VA)
Col B = Attempt Success Category (0 thru 9)
Col C = Actual # of Attemtps

the data looks like this...
Market Attempt Sum
CA 0 5
CA 1 1
CA 2 3
CA 4 5
CA 7 1
VA 9 1
IL 0 4
IL 3 1
IL 5 1
IL 6 1
SC 7 1
VA 8 2

and we'd like to show something like this per market.
CA
Attempt Total
0 5
1 1
2 3
3 0
4 5
5 0
6 0
7 1
8 0
9 0

i can do a sumif but i have to sort and specify the range. for isntance, for
california would use range B2:B105, for IL range B106:B300 and so on. any way
to make it so the range changes dynamiccaly based on column A that has the
market??