Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# counting values down a column

Posted on 2013-11-26
Medium Priority
323 Views
I would like to count down a column, and count the number of different values

what is the best way to do this ?
0
Question by:Europa MacDonald
[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
• 2

LVL 23

Assisted Solution

NBVC earned 1600 total points
ID: 39678837
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

0

Author Comment

ID: 39678862
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

NBVC earned 1600 total points
ID: 39678923
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

andrew_man earned 400 total points
ID: 39681199
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

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.