|
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 |
Count Numbers
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 |
Count Numbers
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 |
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 |
Count Numbers
It is the value you are looking for in column C.
-- David Biddulph "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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 08:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com