Prev: VLOOKUP VALUE ERROR
Next: AutoFilter macro
From: Mayte on 25 May 2010 12:45
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??
From: B Lynn B on 25 May 2010 14:44
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/excel2007/excel2007s7p7.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??
 | 
Pages: 1
Prev: VLOOKUP VALUE ERROR
Next: AutoFilter macro