Sir , I Could not Understand.Could you please tell me in Details

1-What is Zone in the Function which is provided by you. It is Range of my
data i.e A1:E:34

2-Where i put my data.

3-And i could not understand (I would suggest formatting the "outer" cells
so the font is the same color as
the background, making the Zone name invisible. E.G. n1,p1,q1,s1 etc)
4- How can i NAME the Columns of my DATA

Thanks Again.


"Ron Rosenfeld" wrote:

On Thu, 19 Feb 2009 07:23:02 -0800, Pai wrote:

That Was Awesome Ron

Could you pls tell me can i use PIVOT TABEL Mention Below Data With this
Format

North South
1-4 5-7 8-10
Broker
Friends
Dealer
Overall
And Same As Remaining Zone i.e West and East
Zone Broker Friends Dealer Overall
East 1 1 1 4
East 2 2 2 8

I'm glad you could use that.

I don't know how to get a Pivot Table to look like you request.

However, you could set up an area on your worksheet to do that. Here's one
way.

NAME the columns in your data with the column labels.

Set up a table with the following:

$N$1: North
$O$1: North
$P$1: North
$Q$1: East
$R$1: East
$S$1: East
$T$1: South
$U$1: South
$V$1: South
$W$1: West
$X$1: West
$Y$1: West

I would suggest formatting the "outer" cells so the font is the same color as
the background, making the Zone name invisible. E.G. n1,p1,q1,s1 etc.



Note that in order to make the entries in these cells, you must either
pre-format the cells as TEXT, or precede the entry with a single quote.
Otherwise Excel will interpret these entries as dates

$N$2: 1-4
$O$2: 5-7
$P$2: 8-10
$Q$2: 1-4
$R$2: 5-7
$S$2: 8-10
$T$2: 1-4
$U$2: 5-7
$V$2: 8-10
$W$2: 1-4
$X$2: 5-7
$Y$2: 8-10


$M$3: Broker
$M$4: Friends
$M$5: Dealer
$M$6: Overall


Excel 2007
N3:
=COUNTIFS(INDIRECT($M3),"<="&MID(N$2,3,2),INDIRECT ($M3),"="&LEFT(N$2,1),Zone,N$1)

Excel 2003 or earlier:
N3:
=SUMPRODUCT((INDIRECT($M3)<=--MID(N$2,3,2))*
(INDIRECT($M3)=--LEFT(N$2,1))*(Zone=N$1))

Then Fill down N3:N6

Select N3:N6 and fill right to Y3:Y6

--ron