Solved

Count the number of records by ID Number in Excel 2007

Posted on 2011-03-10
12
600 Views
Last Modified: 2012-05-11
How do I count the number of records by ID Number (Column A) in Excel using a formula? The file is sorted by ID Number?
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 25

Accepted Solution

by:
Ron Malmstead earned 250 total points
ID: 35098718
=COUNTIF(A1:A10,"1")


Counts the number of times 1 exists in the range ...a1 thru a10
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35098719
I suggest to use a Pivot Table.

Below is the link for a good understanding of how pivot table works.

http://www.timeatlas.com/5_minute_tips/chunkers/learn_to_use_pivot_tables_in_excel_2007_to_organize_data

Sincerely,
Ed
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35098730
...wait.. are you trying to count how many times a specific ID number appears ?..if so the top formula is what you need...

If not...what do you mean "count by id"?

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 33

Expert Comment

by:jppinto
ID: 35099262
You can build a Pivot Table to list the ID's and the count of each one!

jppinto
0
 
LVL 31

Expert Comment

by:gowflow
ID: 35099920
Morinia,

if I read your question carefully, "How do I count the number of record by ID col A in Excel using a formula ? The file is sorted by ID Number I understand that you want to count in Column A the occurences of ID number knowing that the ID numbers are sorted.

If my understanding is correct Based on the small Example:
108      4
108      4
108      4
108      4
109      1
112      1
113      2
113      2
114      1
115      2
115      2
122      2
122      2
127      2
127      2

Given that you have your ID's in Col A and the result in Col B your formulas should be:
=COUNTIF($A:$A,A1) and drag it all the way down for as many ID as you have.

Rgds/gowflow
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35108414
Allow me to disagree on the accepted answer! This, in my opinion, is not the best solution for your problem because if you have duplicate ID's like on the example posted, and put the COUNTIF formula on the 2nd column you will be returning duplicate counts also. If you use a Pivot Table for that, the result would be something like this instead:

108   4
109   1
112   1
113   2
114   1
115   2
122   2
127   2

I thought that this is what you wanted...that's why me, and MINDSUPERB suggested a Pivot Table instead of a Countif solution.

I would like to ear your feedback please.

jppinto
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35109292
"Allow me to disagree on the accepted answer!"

..... Allow me to do the same since I was the one who posted that formula in the first place.
....gowflow, just posted the formula I had already posted.
0
 

Author Comment

by:morinia
ID: 35113918
I need to reassign the points.  There was an oversight on my part.  
0
 
LVL 31

Expert Comment

by:gowflow
ID: 35115952
I have no problem with the points being re-assigned which ever way the asker deem appropriate. One comment though I don't think that the countif is a formulas that belong to one person !!! xuserx2000: you did use countif correct but your example simply counted as you stated: Counts the number of times 1 exists in the range ...a1 thru a10  and is not what the asker needed !
rgds/gowflow
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35127008
I stick with my previous post! I don't think that the COUNTIF formula be the best way to do this, Pivot Table would do a better job. I still don't agree with the accepted answer!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 35127411
ippinto, the best way to do this is what is convenient to the asker not what one can find as best ! totally disagree with your statement as you are proposing a solution viz pivot table that could not be convenient to the asker as maybe they are not willing to venture in !!! it is also like suggesting to them a VBA solution that also could be judged as best of all !!!!
gowflow
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question