Count The Number Of Unique Numbers In A Column?

Apr 7, 2006

I have a column where I have record numbers. The record number can end up duplicate in some cells.

PROBLEM_ID
IBM-02204732
IBM-03252238
IBM-03252238
IBM-03335648
IBM-03534918
IBM-03534918
IBM-03862015
IBM-03862015
IBM-12737629
IBM-12737629
IBM-17269762

I would like the get a formula that can count the number of unique numbers in that column?

I would like to get if possible to get a formula that summarize the number into a single cell.

View 9 Replies


ADVERTISEMENT

Count The Number Of Unique Numbers In A Column

May 4, 2006

i'm currently trying to count the number of unique numbers in a Column using this formula:

=SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))

So far so good.....But I want to add some conditions like only count the numbers that meet the condition say N1:N3000="SE*"

=SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&""))))

On this one I get #VALUE! and i can't figure out why.

I have also tried to put the AND/OR condition before the actual starting of the array of the SUMPRODUCT but nothing.

View 14 Replies View Related

Write Formula That Will Count Number Of Unique Occurrences In Column?

Nov 2, 2011

I'm trying to write a formula that will count the number of unique occurrences in a column, if a specified value is found in a different column.

So I want to count the number of unique values in the "ID" column if let's say the text "NameA" appears in the "Name" column.

ID Name 12345

NameA
NameB
NameA 12346

View 5 Replies View Related

Sumproduct (count The Number Of Unique, Or Distinct, Company Names In Column)

Jan 28, 2010

I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.

I should also point out that most company names appear multiple times in column [C7].

a) Count the unique company names in column [C7]...

b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records

c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records

Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.

View 2 Replies View Related

Count Number Of Batches Of Consecutive Numbers In A Column?

Oct 11, 2012

What I am trying to is to count the number of times a certain number or character appears (either on its own or in a batch of consecutive cells containing that number/character) in a column.An example might clarify things (for reasons of brevity I will write the columns in rows):

If a column looks like (each 1-digit numbers / characters being a consecutive cell) 0 0 X X 0 0 X and I am counting for X, then I should get 2. If my column is X X 0 X X 0 X 0 0, then I should get 3. If my column is 0 X X 0 0 X 0 then I should get 2. If my column is X X 0 X X 0 X then I should get 3. Is there a formula to perform that calculation?

View 1 Replies View Related

COUNT THE UNIQUE NUMBERS

Feb 8, 2007

I have a column full of Invoice No's. I want to count the number of Invoices that are unique, ir avoiding duplicates.

I know I can use the Advanced filter facility but I'd rather do it in a formula if possible.

View 9 Replies View Related

Count Unique Numbers ..

May 4, 2007

I'm working in Excel 97, trying to use a formula I found in the archives here to count unique numbers in a column.

=SUMPRODUCT((V2:V10000"")/COUNTIF(V2:V10000,V2:V10000&""))

I'm not exactly sure what this formula is doing, but it seems to work reasonably well, except that it's adding one to my total.

i.e. I have a total of 15 and it returns a value of 16 or I have 0 and it gives me 1.

View 9 Replies View Related

Formula To Count Unique ID Numbers Per Month?

Jan 16, 2014

I need to count how many unique ID numbers there are per month.I have figured out how to count the unique ID's for the first month (Jan) but after that I'm having trouble trying to get the count right for Feb and Mar. Formula I used in cell E2 is: {=SUM(IF($A$2:$A$14

View 7 Replies View Related

Sum & Count Of Unique Numbers Meeting Criteria

Jan 28, 2009

I have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information. I'm looking for a formula which can give me the following: How many unique phone numbers is there in column B from service provider number 4 and 5? The following pic is just an example of how the list is compiled. How can I get sum up only the numbers from 4 and 5 and not the whole bunch? If I use this formula

Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
=SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0)). I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean. This was the first part. The second part is finding out how many times each unique number from service provider 4 and 5 is listed.

View 4 Replies View Related

Count Of Unique Numbers Based On A Criteria

Feb 17, 2008

My data is like:

A1: 5 -- B1: X
A2: 5 -- B2: X
A3: 5 -- B3: Y
A4: 3 -- B4: Y
A5: 3 -- B5: X
A6: 8 -- B6: Y
A7: 2 -- B7: X
A8: 3 -- B8: Y
A9: 1 -- B9: X

I want the count of unique numbers in column A based on the criteria i have on column B.
So the count for X should be 4 and for Y should be 3..

Can i achieve this using formula only?

View 5 Replies View Related

Search For All Unique Numbers Down A Column And Print List Of Those Numbers In Another

Jun 5, 2014

Say column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.

View 5 Replies View Related

Count The Number Of Unique Companies?

Aug 19, 2014

I want to count the number of unique companies that joined in different months. I have two columns to work with - date [in time stamp/data format] and company name.

DATE
COMPANY NAME

10:42 02/01/14
A

11:33 02/01/14
B

16:14 07/01/14

The results should equal -

Jan 14 - 2
Feb 14 - 2
Mar 14 - 1

The closest I have gotten it to remove duplicates =SUMPRODUCT((B1:B15<>"")/COUNTIF(B1:B15,B1:B15&"")) and to count the months =COUNTIF(A1:A15,"*01/14") but I cant seem to link the two.

View 14 Replies View Related

Unique Change Number Count

Mar 23, 2009

I am looking to be able to count if prices have changed between periods

See the example below, I am assuming it is an If statement...

View 14 Replies View Related

Count Unique Frequencies Column A For Pacific Values Column B

Dec 19, 2010

I would need to count unique frequencies in column A, but only for a specified value in column B.

Example:

A|B
1|1
1|1
1|1
2|1
2|1
2|2
2|2
3|2
3|2

I am using Excel 2007 and found this formula that counts all unique frequencies in column A: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

In my example, result = 3 Now, I would need to add the possibility to count only for B=1; in my example, result should be = 2.

View 9 Replies View Related

Count Unique Names Only Not The Number Of Times It Appears?

Apr 4, 2013

I've got a set of data that I update once a month and the number of team members per team changes all the time. I'm trying to write a formula that basically says, if the date matches AND the manager name matches, count the number of team members.

In the attached sample if A2 and B4 are found in the data set, count the number of SalesReps they have. So I'm looking at Sarah for February 2013, she has two sales reps that sold something, but Katherine appears twice, so I'm not looking for a result of 3, the correct answer is 2. How do I write the formula?

A2 will look to the data range of A14:A23 and SarahK will look to I14:I23, but I want to count H14:23.

View 9 Replies View Related

Unique Count Number Of Users In Month Regardless Their Frequency

May 29, 2014

I have a signup record that expected to grow up to row 400. On the top, there's a monthly tally for reports. Column B to count the number of boxes taken and Column C for the users picked up the boxes.

Users should be accounted as one appearence in a month, regardless re-appearance in the same month or different month.

I haven't found the generic formula to uniquely count the number of users regardless their frequency in a month.

NOTE: Timings, box numbering format and usenames are randomly generated. Any similarities with real subjects will be coincidence only.

View 4 Replies View Related

How To Count The Number Of Unique Values In A List, Based On Another Value

Jul 27, 2009

I need to count the unique values in a list based on another value. i.e.

AB ___ Jones
AB ___ Smith
BC ___ Henry
AB ___ Jones
BC ___ Brown
BC ___ Henry
AB ___ Tomkins

For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting:

=IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100)))

but haven't been able to get it to work.

View 9 Replies View Related

Count Number Of Rows With Unique Entries In 2 Columns

Oct 9, 2006

I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.

I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).

I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.

All the cust numbers are unique so would I be able to use a wildcard?

View 4 Replies View Related

Count Unique Occurrences In Column A If Column B Equals Value?

Jul 25, 2014

how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.

..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York

View 10 Replies View Related

Count Unique Values In One Column If Other Column Matches?

Oct 18, 2011

How do I count unique values in Column A only if the values in Column B match?

A B
1 a
2 a
1 a
4 b
5 b
1 b

The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.

View 5 Replies View Related

Count Number Of Unique Values Based On Multiple Conditions

Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

View 4 Replies View Related

Count Number Of Unique Values After 1st Part Of Cell Matches?

Aug 8, 2014

Have numerous values in Col A. Col E extracts a list of unique values from that column.

In Col C, the Col A value has had characters added to it.

Need a formula to count the number of unique values from Col C which contain the same prefix from Col A, and place the result in Col F.

A sample workdook is attached with the desired result shown and highlighted in yellow.

View 9 Replies View Related

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

View 14 Replies View Related

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.

View 9 Replies View Related

How Can I Count How Many Unique Data Has My Column

Feb 14, 2009

I have a column on sheet with has some data. I want to know: How many unique data has my column. If I use auto filter I can count it in list. But how can I do it with macros? I don’t want to make a cycle because it will takes me a lot of time…..

View 9 Replies View Related

Formula That Creates Unique Number For Repeating List Numbers?

Jun 26, 2013

I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.
Project Work Id

8069000
8069000
8069000
8069000.1

[Code]....

View 5 Replies View Related

Count Unique Values Based On Another Column

Jul 1, 2009

I am using Excel 2007, and I'm stuck with this problem.

View 4 Replies View Related

Count Unique Words Appearing In A Column Once Per Row

Jan 16, 2010

I would like to modify the below macro so it only counts a word once even if it appears more than once in a row. The reason I want to do this is so I can get a snapshot of the data without certain terms being over-represented.

For example, in the attached example the word 'Microsoft' appears 5 times in row 4 but I only want this to be counted once. In the whole data set Microsoft appears 20 times but only in 7 of the 20 rows so I would like the count to be 7.

The example is set out as follows: In column B there is a description field which in practice will contain consumer complaints and inquiries. To keep the data anonymous the description is filled out with random words and all other columns are blank. The output of Andy Pope's unique word counting macro appears in I:J.

View 12 Replies View Related

Count Unique Values In A Filtered Column

Dec 29, 2008

I need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A.

View 3 Replies View Related

Count Unique Strings In Excel Column

Apr 3, 2012

I have a long list of products in an Excel column. I want to count the number of unique items in the list (the cells contain strings, not values).

I know I can use the "Remove Duplicates" tool in the Data menu, and then use a Count function on the resulting list. I wondered if there was an Excel function or formula that would do it, similar to the Frequency function that does the same thing with a list of values.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved