ExcelBanter - Count Numbers

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Count Numbers (https://www.excelbanter.com/excel-worksheet-functions/221456-count-numbers.html)

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

Hi,

Easy way would be a pivot table with numbers in the Column field and Data
area, Zone in the row area. The group the column Field numerically or
manually.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"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

Hi,

If you want a formula approach,

In 2007 you can use something like

=COUNTIFS(A2:A29,"East",B2:B29,"<5")
and
=COUNTIFS(A2:A29,"East",B2:B29,"<8",B2:B29,"4")

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"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

Hi,

And in 2003 or earlier:

=SUMPRODUCT(--(A2:A29="East"),--(B2:B29<5))
or
=SUMPRODUCT(--(A2:A29="East"),--(B2:B29<8),--(B2:B294))

or better replace East with a cell reference.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"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

Hi,

Build a table that looks like this

1-4 5-7 8-10
North
South
East
West

Mine is in F1 to I5

Put this formula in G2
=SUMPRODUCT(($A$1:$A$33=$F2)*($B$1:$B$33=LEFT(G$1 ,1)+0)*($B$1:$B$33<=(MID(G$1,FIND("-",G$1)+1,LEN(G$1))+0)))

Drag down to g5 then select all four cells and drag right to column I

Note that in the column Headers the header is 1-10 with no spaces

You should end up with this

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

Mike

"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