| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number of different items in a column
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Number of different items in a column
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) wrote in message oups.com... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Number of different items in a column
Thank you Bob so much!
is SUMPRODUCT or Countif an Excel Function? please explain it to me |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Number of different items in a column
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) wrote in message ups.com... Thank you Bob so much! is SUMPRODUCT or Countif an Excel Function? please explain it to me |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| line up items in column a with items in columns b, c, etc | Excel Discussion (Misc queries) | |||
| Grouping a column of data and displaying the number of items grouped | Excel Worksheet Functions | |||
| SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
| Count number of items in one column that have a value in another? | Excel Worksheet Functions | |||
| Count number of unique items in a column that contains duplicates | Excel Worksheet Functions | |||
Linear Mode
