# Counting Number Of Unique Items In Column

Oct 10, 2008
I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data

a1 = apples

a2 = pears

a3 = oranges

a4 = apples

a5 = apples

a6 = apples

a7 = pears

in this case number of uniques items is 3

Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.

View 6 Replies
ADVERTISEMENT
Sep 15, 2008

I want to paste a list of customers into a sheet and then have a formula that counts the number of customers. So, even if a customer appears 10 times it would only count it once and and continues down the column and counts the next company name and so on. Then it gives me a total of the number of different companies in that column.

View 9 Replies
View Related
Oct 3, 2007

I have a list of around 50000 names. I need to count how many are unique.

View 9 Replies
View Related
Jul 17, 2009

I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

View 9 Replies
View Related
Aug 3, 2006

I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.

so,

column A has "30 diffrent names repeated from A2 to A101"

column B has numbers 0 to 100

column c has value either 0 or 1

now i use an formula TO filter out rows in column B & c

formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter

now what should i suffix or prefix to this formula to count unique values in column A.

View 11 Replies
View Related
Feb 3, 2010

I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records.

To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.

View 3 Replies
View Related
Nov 5, 2008

I need a formula which can count the number of items which have been applied to a suspense account in between a set of dates so for example i may need all items placed into a suspense account in the month of october. i record all items in a list so i do have a list of dates i just need to know how to count them and keep a running total as i need to feed it into another spreadsheet. I will also need to sum these the format of the spreadsheet is below -

******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Book4___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2K2B3K3B4K4B5K5B6K6B7K7B8K8=ABCDEFGHIJKL1MOPO/S Date AppliedValue DateRemovedValue AgeCompany Name2Cheque 01-Nov-08(15.55) 01-Nov-08 15.55 0abc3Direct Receipt 01-Nov-08(109.00) 01-Nov-08 109.00 0def4Cheque 02-Nov-08(50.30) 02-Nov-08 50.30 0ghi5Cheque 03-Nov-08(23.48) 03-Nov-08 23.48 0jkl6Direct Receipt 03-Nov-08(2,236.46) 03-Nov-08 2236.46 0mno7Cheque 03-Nov-08(212.11) 03-Nov-08 212.11 0pqr8BACS/CHAPS 04-Nov-08(58.75) 04-Nov-08 58.75 0stuSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Jan 19, 2014

1. I use this formula to count a number of items in a column per month: =SUMPRODUCT(--(MONTH(B6:B500)=8))

I also have another column C where I put some descriptors against the values in column B. I need a formula to calculate a number of items in Column B, except for those items, which are equal to XYZ in column C.

2. I also calculate a number of items, which were completed in a given month. Completion is marked with "Y" in a separate column (D). I use this formula: =SUMPRODUCT((YEAR(B6:B999)=2013)*(MONTH(B6:B999)=12)*(K6:K999<>"Y")). I can't make it count a number of completed items in January. C

View 8 Replies
View Related
Feb 10, 2007

I found the following formula online while searching for how to count unique instances of a number in a list. The list may have blank cells or repeated numbers but this formula works. =SUMPRODUCT((A1:100<>"")/COUNTIF(A1:A100,A1:A100&""))

So my question is...can someone verbally explain just exactly what is going on? i.e. I don't understand the <>"" in the sumproduct argument or the &"" in the second argument of the countif.

View 5 Replies
View Related
May 19, 2006

In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.

View 9 Replies
View Related
Apr 2, 2012

I need to be able to count items if they match a condition in another column, as below

A B

1 Apple Red

2 Apple Green

3 Apple Red

4 Pear Green

5 Apple Red

6 Pear Green

7 Pear Red

8 Apple Red

How can I count how many Red Apples there are. The answer should be 4 but when I use countifs I can either count the no of apples in Col. A or the no of colour Red in Col. B.

I have tried every combination of if, vlookup, and count that I can but keep getting errors or single col. counting.

View 3 Replies
View Related
Aug 15, 2014

It is about finding the number of unique items in a range that contains empty cells.I can intuit the form of this:

Formula:

[Code]....

And many of the variations of it when there are no empty cells in the range.

For a range of that contains empty cells I've tried this:

Formula: [Code] .....

And variations of it. It does not work, and I don't know why...............however..........

I ran across this on another [SOLVED] thread and it does work:

Formula: [Code] .....

I would have never considered this plausible. What is the magic behind appending an empty string to the criteria in the COUNTIF function?

It strikes me that this strategy likely has application elsewhere, too.

View 14 Replies
View Related
Dec 7, 2007

Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.)

basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.

View 9 Replies
View Related
Apr 24, 2014

I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.

I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.

However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....

View 14 Replies
View Related
Mar 6, 2003

I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.

View 9 Replies
View Related
Jan 25, 2012

I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)

So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?

View 1 Replies
View Related
Dec 19, 2006

I am trying to seperate singlr column information into seperate column based on the name in the Data 1 column.

Ex.

Data1 Data2 Result 1 Result 2 Result 3

one100one100two200three1

one350one350two450three2

one500one500two600three4

two200

two450

two600

three165

three236

three450

View 3 Replies
View Related
Jan 17, 2008

How can I count the number of unique values in a column?

I have a spreadsheet that lists transactions that occurred over a one-year period (2007). I need to determine the number of days our facility was operational. No transactions on a particular date (25-Dec-07, for example) and the facility would be considered to have been closed on that date.

On the days that the facility was open, multiple transactions would have occurred on those open days and those multiple tranactions are all reflected in the worksheet.

So I need to count each occurrence of '01-Jan-07', '02-Jan-07', and so on.

View 9 Replies
View Related
Jun 9, 2014

I need to count all of the unique value in column C( Supplier Name) If the Owner is Analyst on column D and the Buyer is Mark column A.

I would be needing a formula to count it and not pivot table. I dont know how to set up an array and I just have learn how to use the Countif formula.

Buyer

Parts

Supplier name

Owner

[Code]......

View 3 Replies
View Related
Dec 17, 2009

I'm trying to get a forumla to tell me if a filtered column is showing a unique entry.

suppose column A has as follows

A1 - Bob

A2 - Bob

A3 - Sam

A4 - Bob

A5 - Sam

Now, if I filter by ANY column, I want to have a formula that will tell me if the filtered/visible name(s) in column A is only 1 name. Mind you, if the user filters by criteria in a another column, the unique name may show in column A, only once or multiple times depending on how the other column is filtered.

I've tried count, counta, countif and such but doesn't seem to deal with what I am trying to do.

View 9 Replies
View Related
Feb 13, 2014

im looking to have a drop down list on sheet2 that displays unique items in column A on sheet 1.

then when an item is selected from the drop down list, i want to be able to display all unique values that correspond to that option. i have attached an example worksheet

data on sheet1 is ever changing

View 4 Replies
View Related
Aug 21, 2014

Formula which will count unique values in column A based on condition in Column B which "y" .....

Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....

View 3 Replies
View Related
Jun 26, 2014

I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.

I have tried different formulas, but fast running out of time

I have attached a modified sample from the main spreadsheet

I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.

View 7 Replies
View Related
Dec 20, 2013

I am looking to have the items in column K 1 through 16 listed from highest to lowest and at the same time post the corresponding number from column J 1 through 16.

So that it reads as follows:

04 - 18 - 06

02 - 17 - 06

08 - 10 - 03

Item 04 has 18 points and is listed in 6 columns etc.

How can I get Excel to do this?

View 5 Replies
View Related
Oct 4, 2006

how can I count the number of not empty rows(and for example too many) using do.. until loop kind. I am searching on the net for about 24 hours but I couldn't find it.

View 5 Replies
View Related
Mar 7, 2014

We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.

Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.

In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.

TYPE

MTD Closing Date

Status[code]......

View 9 Replies
View Related
May 2, 2006

I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:

column a = m454 column b = fsh454-1

m543 fst998-2

m998 fsm434-1

my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.

View 4 Replies
View Related
Mar 28, 2008

Need to find a way to flag the unique number in column with 641892

2057988-0380121611AAAAA########70002057988-0380121611641892########59262057988-0380121611642176########53442057988-0380121611646570########63032057988-0380121611645812########56232057988-0380121611643573########55912057988-0380121611640502########62482057988-0380121611645723########58592057988-0380121611645818########61662057988-0380121611641295########55712057988-0380121611643574########61452057988-0380121611640079########63042057988-0380121611640450########61252057988-0380121611645494########57392057988-0380121611644573########51832057988-0380121611643595########51842057988-0380121611645404########56442057988-0380121611646669########57812057988-0380121611646890########63052057988-0380121611646013########63022057988-0380121611646755########51822057988-0380121611642278########52732057988-0380121611641892########79262057988-0380121611640787########51802057988-0380121611640774########5426

View 26 Replies
View Related
Dec 19, 2012

I have this data lets say id denotes a particular house and the multiple entries of the same house are for different members of a house. Now i want to calculate the total no of people who earn in a particular household i.e if a cell is filled or not.

idB C

00118000040000

001

001

001

002170000

[code].....

View 2 Replies
View Related
Mar 21, 2012

I want to count the number of cells in column A that are not blank MINUS or EXCLUDING the cells in Column AD whose values are "Closed".

View 9 Replies
View Related