# 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.

## Counting Unique Number Of Items

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.

## Counting The Number Of Unique Text Strings In A Column

Oct 3, 2007

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

## Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

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.

## Counting Unique Items(values Or Text)

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.

## Counting Items Unique To A Day/customer Combination

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.

## Counting Number Of Items In Between Dates

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.

## Formula For Counting A Number Of Items But Excluding Some Of Them?

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

## Counting Unique Instances Of A Number

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.

## Selecting Unique Column Items Into Seperate Worksheet From Column

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.

## Counting Items If They Match A Condition In Another Column

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.

## Finding The Number Of Unique Items In A Range That Contains Empty Cells?

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.

## Count Unique Items In A Column

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.

## Counting Number Of Unique Numbers Based On Another Variable?

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) .....

## Counting Number Of Unique Values In Rows/columns

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.

## Display Unique Items That Occur More Than X Times In Column?

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?

## Create Column Of Data Based On Unique Items

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

## Counting Unique Values In A Column?

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.

## Counting Unique Values With Reference To Other Column?

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.

Parts
Supplier name
Owner

[Code]......

## Counting Unique Entires In A Filtered Column

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.

## Dropdown List On Sheet2 That Displays Unique Items In Column A On Sheet 1?

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

## Counting Unique Values Based On Condition In Different Column?

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 .....

## Counting Unique Values From Column Meeting Multiple Criteria?

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.

## Rank Items In Column K From Highest To Bottom With Corresponding Number In J

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?

## Counting The Number Of Used Rows In Column

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.

## Use The COUNTIF Function Formula To Count The Number Of Items In Column A?

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]......

## Comparing Two Column For Matching Number But The Items Compared Also Contains Letters

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.

## Unique Number In Column

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

## Counting Number Of Filled Columns According To Another Column

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].....

## Counting Number Of Cells In Column That Are Not Blank

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".