#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default Count Numbers

HI! Expert
I have data in Two Column Like This:
Zone No.
East 4
East 8
East 6
East 31
East 5
East 9
East 3
East 9
North 1
North 5
North 9
North 7
North 2
North 6
North 10
North 5
North 10
South 2
South 6
South 10
South 8
South 3
South 7
South 1
South 4
West 3
West 7
West 5
West 9
West 4
West 8
West 2
West 8
Now i Want the total number based on Zone But i different Way

Like This: 1to4,5to7,8to10

Zone East has Number from 1to 10 or Sometime 1to7or8

Now i want a count of number Between 1 to 4 like In my Example:

Zone 1-4 5-7 8-10
North 2 4 3
East 2 2 4
South 4 2 2
West 3 2 3

i.e. how many times numbers 1 to 4 have appear in East Zone or any other
Zone. And also 5to 7 and 8to 10.

I hope you guys will understand my Problem.

Thanks in Advance

Hardeep kanwar
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default Count Numbers

Thanks Don

You Really save my lots of time. I have 18 Sheets like this Data.

thanks Again

Hardeep kanwar

"Don Guillett" wrote:


The F column has the names.
--
zone
North
East
South
West


Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hardeep_kanwar" wrote in message
...
Thanks for Quick Reply

Could you please tell me what is F2 in this Function

"Don Guillett" wrote:

This will do it for 1:4 where col c has the zones and col D has the
numbers.
Copy down
=SUMPRODUCT(($C$2:$C$100=F2)*($D$2:$D$100=1)*($D$ 2:$D$100<5))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hardeep_kanwar" wrote in
message
...
HI! Expert
I have data in Two Column Like This:
Zone No.
East 4
East 8
East 6
East 31
East 5
East 9
East 3
East 9
North 1
North 5
North 9
North 7
North 2
North 6
North 10
North 5
North 10
South 2
South 6
South 10
South 8
South 3
South 7
South 1
South 4
West 3
West 7
West 5
West 9
West 4
West 8
West 2
West 8
Now i Want the total number based on Zone But i different Way

Like This: 1to4,5to7,8to10

Zone East has Number from 1to 10 or Sometime 1to7or8

Now i want a count of number Between 1 to 4 like In my Example:

Zone 1-4 5-7 8-10
North 2 4 3
East 2 2 4
South 4 2 2
West 3 2 3

i.e. how many times numbers 1 to 4 have appear in East Zone or any
other
Zone. And also 5to 7 and 8to 10.

I hope you guys will understand my Problem.

Thanks in Advance

Hardeep kanwar






  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Count Numbers

On Wed, 18 Feb 2009 06:48:10 -0800, Hardeep_kanwar
wrote:

HI! Expert
I have data in Two Column Like This:
Zone No.
East 4
East 8
East 6
East 31
East 5
East 9
East 3
East 9
North 1
North 5
North 9
North 7
North 2
North 6
North 10
North 5
North 10
South 2
South 6
South 10
South 8
South 3
South 7
South 1
South 4
West 3
West 7
West 5
West 9
West 4
West 8
West 2
West 8
Now i Want the total number based on Zone But i different Way

Like This: 1to4,5to7,8to10

Zone East has Number from 1to 10 or Sometime 1to7or8

Now i want a count of number Between 1 to 4 like In my Example:

Zone 1-4 5-7 8-10
North 2 4 3
East 2 2 4
South 4 2 2
West 3 2 3

i.e. how many times numbers 1 to 4 have appear in East Zone or any other
Zone. And also 5to 7 and 8to 10.

I hope you guys will understand my Problem.

Thanks in Advance

Hardeep kanwar


You could also use a Pivot Table

Drag zones to Row
Drag No to columns
Drag No. to data area

Change Sum of No. to Count of No.

Select a column label, then
Group
starting at 5
ending at 10
By: 3

(By starting at 5, the values below 5 will be 1-4)

Then relabel the appropriate headers.

My result:

Count of No. Nos.
Zones 1-4 5-7 8-10 11 Grand Total
North 2 4 3 9
East 2 2 3 1 8
South 4 2 2 8
West 3 2 3 8
Grand Total 11 10 11 1 33

Note that there is an entry for 11 since one of your entries is 31.
--ron
  #8   Report Post  
Posted to microsoft.public.excel.misc
Pai Pai is offline
external usenet poster
 
Posts: 18
Default Count Numbers

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
East 1 1 1 6
East 2 1 1 10
East 1 3 3 5
East 2 4 10 9
East 3 3 3 3
East 3 2 2 9
North 1 2 1 1
North 1 1 3 5
North 1 2 1 9
North 1 1 2 7
North 3 2 1 2
North 3 1 2 6
North 2 3 1 10
North 1 3 2 5
North 5 2 3 10
South 1 2 3 2
South 6 9 9 6
South 1 2 1 10
South 1 1 1 8
South 3 2 1 3
South 1 3 2 7
South 3 3 1 1
South 7 1 2 4
West 8 10 5 3
West 1 1 2 7
West 9 10 3 5
West 1 3 3 9
West 10 3 1 4
West 1 2 1 8
West 1 1 3 2
West 3 2 3 8

Thanks In Advance



"Ron Rosenfeld" wrote:

On Wed, 18 Feb 2009 06:48:10 -0800, Hardeep_kanwar
wrote:

HI! Expert
I have data in Two Column Like This:
Zone No.
East 4
East 8
East 6
East 31
East 5
East 9
East 3
East 9
North 1
North 5
North 9
North 7
North 2
North 6
North 10
North 5
North 10
South 2
South 6
South 10
South 8
South 3
South 7
South 1
South 4
West 3
West 7
West 5
West 9
West 4
West 8
West 2
West 8
Now i Want the total number based on Zone But i different Way

Like This: 1to4,5to7,8to10

Zone East has Number from 1to 10 or Sometime 1to7or8

Now i want a count of number Between 1 to 4 like In my Example:

Zone 1-4 5-7 8-10
North 2 4 3
East 2 2 4
South 4 2 2
West 3 2 3

i.e. how many times numbers 1 to 4 have appear in East Zone or any other
Zone. And also 5to 7 and 8to 10.

I hope you guys will understand my Problem.

Thanks in Advance

Hardeep kanwar


You could also use a Pivot Table

Drag zones to Row
Drag No to columns
Drag No. to data area

Change Sum of No. to Count of No.

Select a column label, then
Group
starting at 5
ending at 10
By: 3

(By starting at 5, the values below 5 will be 1-4)

Then relabel the appropriate headers.

My result:

Count of No. Nos.
Zones 1-4 5-7 8-10 11 Grand Total
North 2 4 3 9
East 2 2 3 1 8
South 4 2 2 8
West 3 2 3 8
Grand Total 11 10 11 1 33

Note that there is an entry for 11 since one of your entries is 31.
--ron

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Count Numbers

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
  #10   Report Post  
Posted to microsoft.public.excel.misc
Pai Pai is offline
external usenet poster
 
Posts: 18
Default Count Numbers

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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Count Numbers

On Fri, 20 Feb 2009 09:13:03 -0800, Pai wrote:

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


Did you not read where I wrote: "NAME the columns in your data with the column
labels."?

In the data you supplied, Zone appears to be the label of the column in which
you have entries such as North, East, etc. If this is not a label for that
column, please tell me its significance.


2-Where i put my data.


Wherever you like (on the worksheet).


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)


Then don't do it.

4- How can i NAME the Columns of my DATA


Look up "Define and use names in formulas" in HELP.

If you don't want to use NAMEs, use an absolute reference to the requisite
columnar range.

For example, if you have Zone in A2:A475, then, in the formula, in place of
Zone, use $A$2:$A$475


What you are doing is complex enough that you really need to learn some of
these Excel basics, if you are going to be able to support your users
competently.
--ron
  #12   Report Post  
Posted to microsoft.public.excel.misc
Pai Pai is offline
external usenet poster
 
Posts: 18
Default Count Numbers

Dear Sir,
Attached is the Original File of my Question.

http://www.savefile.com/files/2012563

Please do the needful.



"Ron Rosenfeld" wrote:

On Fri, 20 Feb 2009 09:13:03 -0800, Pai wrote:

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


Did you not read where I wrote: "NAME the columns in your data with the column
labels."?

In the data you supplied, Zone appears to be the label of the column in which
you have entries such as North, East, etc. If this is not a label for that
column, please tell me its significance.


2-Where i put my data.


Wherever you like (on the worksheet).


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)


Then don't do it.

4- How can i NAME the Columns of my DATA


Look up "Define and use names in formulas" in HELP.

If you don't want to use NAMEs, use an absolute reference to the requisite
columnar range.

For example, if you have Zone in A2:A475, then, in the formula, in place of
Zone, use $A$2:$A$475


What you are doing is complex enough that you really need to learn some of
these Excel basics, if you are going to be able to support your users
competently.
--ron

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Count Numbers

On Sat, 21 Feb 2009 07:18:05 -0800, Pai wrote:

Dear Sir,
Attached is the Original File of my Question.

http://www.savefile.com/files/2012563

Please do the needful.


Sorry, I don't open unsolicited attachments.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count numbers and non numbers(conditional values) Ecoman Excel Discussion (Misc queries) 5 May 9th 08 04:56 PM
How do i count numbers and letters to find a total count of all Linda Excel Worksheet Functions 4 November 10th 05 05:51 PM
Number count - postcodes and need to count the numbers of tim... Mark - Aust Excel Discussion (Misc queries) 1 October 24th 05 10:00 AM
Count comma separated numbers, numbers in a range with dash, not t Mahendra Excel Discussion (Misc queries) 0 August 8th 05 05:56 PM
count a group of numbers but do not count duplicates Lisaml Excel Worksheet Functions 2 January 27th 05 12:19 AM


All times are GMT +1. The time now is 10:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"