If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
display and count the number of times a value appears
I am trying to count the number of times each zip code appears in an address
list and have it display each zip code with the count beside it ie 11456 6 20457 5 74653 12 |
#2
|
|||
|
|||
display and count the number of times a value appears
Nel post
*dbath* ha scritto: I am trying to count the number of times each zip code appears in an address list and have it display each zip code with the count beside it ie 11456 6 20457 5 74653 12 Use the COUNTIF function: if your zip codes are in column A starting from A2, in B2 put: =COUNTIF(A:A;A2) then copy down -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
|
|||
|
|||
display and count the number of times a value appears
Thank you for your help but I seem to be missing something I beleive
Maybe this will better illustrate the situation column S column T ZIP count 19046 20009 20016 20904 20913 21117 22041 22903 23462 23602 "Franz Verga" wrote: Nel post *dbath* ha scritto: I am trying to count the number of times each zip code appears in an address list and have it display each zip code with the count beside it ie 11456 6 20457 5 74653 12 Use the COUNTIF function: if your zip codes are in column A starting from A2, in B2 put: =COUNTIF(A:A;A2) then copy down -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#4
|
|||
|
|||
display and count the number of times a value appears
If your addresses are broken out into separate cells (Street in A, City in B and zip in C) you could use a simple Pivot Table. Assuming your header is in C1 and your range is C1:C100. Select the range, click on the Pivot table wizard. When step one appears, click next. Step two should have your range in it if you selected it before opening the wizard so click next. Click on Layout. Your header should appear on the right. Drag that to the Row section and then again to the data section. The item in the data section should say "count of zip" if your header in C1 is zip. Select where you want the Pivot Table to be and click on finish. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=554694 |
#5
|
|||
|
|||
display and count the number of times a value appears
I got the other way to work but I will rty to learn this way as well
I quickly rear about Pivot tables and gave up but your explaination seems fairly easy Thank You "SteveG" wrote: If your addresses are broken out into separate cells (Street in A, City in B and zip in C) you could use a simple Pivot Table. Assuming your header is in C1 and your range is C1:C100. Select the range, click on the Pivot table wizard. When step one appears, click next. Step two should have your range in it if you selected it before opening the wizard so click next. Click on Layout. Your header should appear on the right. Drag that to the Row section and then again to the data section. The item in the data section should say "count of zip" if your header in C1 is zip. Select where you want the Pivot Table to be and click on finish. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=554694 |
#6
|
|||
|
|||
display and count the number of times a value appears
That Pivot Table is the ticket
Once I messed around with it it was great Thanks "SteveG" wrote: If your addresses are broken out into separate cells (Street in A, City in B and zip in C) you could use a simple Pivot Table. Assuming your header is in C1 and your range is C1:C100. Select the range, click on the Pivot table wizard. When step one appears, click next. Step two should have your range in it if you selected it before opening the wizard so click next. Click on Layout. Your header should appear on the right. Drag that to the Row section and then again to the data section. The item in the data section should say "count of zip" if your header in C1 is zip. Select where you want the Pivot Table to be and click on finish. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=554694 |
#7
|
|||
|
|||
display and count the number of times a value appears
You're welcome. Thanks for the feedback. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=554694 |
#8
|
|||
|
|||
display and count the number of times a value appears
use =counif -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514 View this thread: http://www.excelforum.com/showthread...hreadid=554687 |
#9
|
|||
|
|||
display and count the number of times a value appears
I suppose that you have zip codes in col A (A1:A100). enter manually zip codes in col B only once as you shown in your question then in col C enter following formula. =COUNTIF($A$1:$A$100,B1) and copy it down in col C untill your data in col B ends. hope this will solve your problem. dbath Wrote: I am trying to count the number of times each zip code appears in an address list and have it display each zip code with the count beside it ie 11456 6 20457 5 74653 12 -- starguy ------------------------------------------------------------------------ starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434 View this thread: http://www.excelforum.com/showthread...hreadid=554687 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how do i count text and display it as text plus the # times it hap | Count in Excel | New Users | 1 | April 26th, 2006 10:15 AM |
Display current record number on a subform | Access Joe | Using Forms | 5 | December 24th, 2005 01:16 PM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 02:19 PM |
Count and display different services | Simon Jester | Running & Setting Up Queries | 1 | November 23rd, 2004 06:30 PM |
Count and display the Number of "Yes" in various fields | ehale | Setting Up & Running Reports | 6 | September 30th, 2004 04:47 PM |