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:B29>4))

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 G
=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