Solved

counting values down a column

Posted on 2013-11-26
4
275 Views
Last Modified: 2013-11-30
I would like to count down a column, and count the number of different values

what is the best way to do this ?
0
Comment
Question by:MichaelGlancy
  • 2
4 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 400 total points
Comment Utility
Are you trying to count unique values in a range?

eg

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

should count unique values in range A2:A100

adjust ranges to suit.
0
 

Author Comment

by:MichaelGlancy
Comment Utility
I have a list. (for simplicity I have a simple test list where I know what the result shoud roughly be)

1
1
1
2
2
2
2
3
3
3

Id like to end up with a result where all the instances of each value are counted so
1 - 3
2 - 4
3 - 3
0
 
LVL 23

Accepted Solution

by:
NBVC earned 400 total points
Comment Utility
You can create a quick Pivot Table (i.e Insert|Pivot Table), make sure the column has a header first.  Then drag the header title to Row area, then drag it in the Summation area, then click on that and select from Value Field Setting: Count.

Alternatively select the column go to Data, Advanced from Sort & Filter section, then check Unique Values only.  Select Copy to Another location and select that location in the Copy to field.  Click Ok.  A unique listing should show up.  Then you can use COUNTIF to count

=Countif(A:A,H1)

WHERE column A contains the original list, and the unique list starts at H1.

Copy formula down.

Alternatively, manually list all the unique values, say starting in H1, and use the COUNTIF formula above....
0
 
LVL 4

Assisted Solution

by:andrew_man
andrew_man earned 100 total points
Comment Utility
USE #1 FOR THE KEY

=IFERROR(INDEX($A$1:$A20,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$1:$A$20),0,0),0)),"")

USE #2 FOR THE VALUE

=IF(C2<>"",COUNTIF($A$1:$A$20,C2),IF(C1<>"",SUM(OFFSET(D2,-ROW()+1,0,ROW()-1)),""))
Book1.xlsx
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now