Count Numbers

H

Hardeep_kanwar

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
 
D

Don Guillett

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))
 
D

Don Guillett

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


Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
 
H

Hardeep_kanwar

Thanks Don

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

thanks Again

Hardeep kanwar
 
R

Ron Rosenfeld

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
Click to expand...

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
 
P

Pai

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
P

Pai

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.
 
R

Ron Rosenfeld

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
Click to expand...

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.
Click to expand...

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)
Click to expand...

Then don't do it.
4- How can i NAME the Columns of my DATA
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Link a text box based on drop down box with four options MS Word 365 0
Count Numbers 4
Copying data from one workbook to another dependent on user select 1
Preferred way to do sort order - column in table, or specified in query? 3
SEARCH IN SUMPRODUCT 8
ranking question 2
Dynamic drop down list 2
Pivot Tables (% to total within groups) 0

Top