Number of different items in a column

S

sam.fares

How do i write a macro that will tell me how many different items in
one column?

lets say i have all the information in column"A". this information will
be such as these marks "J1","J2","J3" and so on. but J1 or J2 and so on
could repeate in the cells of that column.

how do you write a macro that will help determine how many different
marks in that column?. Thanks alot!
 
B

Bob Phillips

You don't need a macro

=SUMPRODUCT((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1:A1000&""))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

sam.fares

Thank you Bob so much!

is SUMPRODUCT or Countif an Excel Function? please explain it to me
 
B

Bob Phillips

They are both Excel worksheet functions.

COUNTIF counts the number of instances of a value in a range. SUMPRODUCT
adds all of the values in an array (but can also manage multiplying
different arrays).

What it is doing is to for each non-blank cell in the range, it divides each
instance by the number of instances, and then ads them up. SO for instance,
if they are 3 instances of a particular item, it evaluates each instance to
1/3, so when added it gets 1. similarly, 2 instances evaluate to 1/2, again
adding up to 1. In this way, each multiple occurrence is summed as 1,
facilitating the counting of the unique items.



--

HTH

RP
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top