Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Numbers
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Numbers
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Numbers
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Numbers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count numbers and non numbers(conditional values) | Excel Discussion (Misc queries) | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
count a group of numbers but do not count duplicates | Excel Worksheet Functions |