# Count Unique Values / Text Within Range Of Cells That Contain Duplicates / Blanks And Errors

Jun 25, 2014
How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

For e.g., in Column A (row 1 - 10):

Proj-001

Proj-001

Proj-002

Proj-004

#N/A

#N/A

Proj-007

Proj-002

View 3 Replies
ADVERTISEMENT
Nov 29, 2007

Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

View 4 Replies
View Related
Aug 24, 2009

If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?

View 14 Replies
View Related
May 23, 2012

Is there anyway to make this work without having to enter a specific range. For example I want to count the duplicates in column U, but don't want it to count blank cells.

Here is my formula right now, and it works, but it counts all of the blank cells in the row as duplicates. How can I stop that?

=SUMPRODUCT(--(COUNTIF(U:U,U:U)>1))

View 4 Replies
View Related
May 8, 2014

I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.

Example:

AADD

#N/A

BB23EEFF

#N/A

#N/A12GG

CC

Results:

AA

BB

CC

DD

EE

FF

GG

View 7 Replies
View Related
May 14, 2012

I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:

Code:

=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))

).

The answer should be 4,457.

Ticket Number

T20110819.0527

T20110830.0339

T20110901.0060

T20110901.0060

T20110907.0042

T20110907.0042

T20110908.0186

T20110908.0186

T20110908.0186

T20110908.0186

[code].....

View 1 Replies
View Related
Jul 10, 2013

I am attempting to count how many cells have different values in a table while not counting the N/A's and 0's found in the table. From what I'm finding online, I see lots of formulas set up with frequency functions, but none of them are set up to exclude anything - just find unique values. I need to count the cells with values other than 0 and N/A going across each row and not count the same value twice

Here's an example of what the table looks like (the real one is over 1,000 rows and 50 columns) with column C being where the formula needs to go. I put what I would like to have returned in the cells.

C

D

E

F

G

H

5

2

N/A

N/A

0

43263526AF

324GFDS

[Code] ........

View 5 Replies
View Related
Jan 6, 2008

I have the following data in 1 of the tabs.

[TABLE]

ClassSequenceAB

10SE132422

20SE23212

20SE321

20SE23425

10SE332455

15SE132412

10SE234

[/TABLE]

I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:

[TABLE]

ClassAB

1032

1511

2012

[/TABLE]

Can this be achieved through a formula?

View 8 Replies
View Related
Aug 11, 2014

I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.

Company Name

Company Type

Company A

SME

Company B

SME

(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.

View 5 Replies
View Related
Nov 15, 2013

I have some data in a column, starting at row 6 that I'd like to find the number of unique text values for. For this I have been using this formula

Code:

{=SUM(IF(FREQUENCY(IF(LEN(A6:A10000)>0,MATCH(A6:A10000,A6:A10000,0),""), IF(LEN(A6:A10000)>0,MATCH(A6:A10000,A6:A10000,0),""))>0,1))}

The data is dynamic so I picked an arbitrarily large number (10000) and the above formula successfully ignores blank fields.

However, I'd like to now find the count of unique text values that end in X. For example, let's say the data are as follows

Dog_Cat

Mouse_Dog

Mouse_Cat

Mouse_Cat

Mouse_Cat

Mouse_Mouse

Elephant_Elephant

How would I go about (in one function) finding the unique values that end in "_Cat", in this case 2 ("Mouse_Cat" and "Dog_Cat")?

View 5 Replies
View Related
Jan 25, 2010

I need a one formula solution to compare two columns for duplicates and count the number of matched pairs. Need to ingore blanks. Can have matched numbers or letters. Asterik denotes a blank (empty) cell. Also only consider the first matched pair if there are more than one matched pairs.

Col A Col B

a

b a

* b

c c

d y

x f

f z

f z

In this case I only want to consider a:a, b:b, c:c and f:f, resulting in the count of 4.

View 9 Replies
View Related
Feb 27, 2008

I am having 2 problems with dynamic named ranges. On one hand, I am getting a LOT of duplicates in some ranges and a lack of entries in those ranges that have too many blanks. Here is a sample of the dynamic named range in the first column:

This first range is called "NamedRange_1"

=OFFSET(Data!$A$2,0,0, COUNTA(Data!$A:$A)-1,1)

how to eliminate both the duplicates and the blanks?

View 6 Replies
View Related
Mar 14, 2007

I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total $ amount of each amount attached to each occurrence.

Please look at the sample to see what I mean.

W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record.

I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs.

Dim rCell As Range, rRng As Range, vKey, lrow As Long

Set rRng = Range("F2:F199")

With CreateObject("Scripting.dictionary")

.comparemode = vbTextCompare

View 8 Replies
View Related
Oct 3, 2011

I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range

I am using the following array formula to establish an overall count:

=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))

Ctrl+Shift+Enter

The date column is E:E.

The store number column is G:G

View 3 Replies
View Related
Jul 8, 2014

I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.

ex.

SHEET1

COLUMNA

Bill

[Code]....

My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))

where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.

View 3 Replies
View Related
Feb 28, 2007

I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?

I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).

(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)

View 9 Replies
View Related
Jan 26, 2009

I have use for this function on varying ranges. I pasted my function as well as my call to it. PhasesActive is just a named range of 5 cells. I get an error... by ref argument type error. Something with the argument, do I have to name the worksheet the range is on?

Function RangeValueCount(Rng As range)

'The function to check if a range has more than one value marked for 'selection, ex: The phases choices

For Each cell In Rng

If Not IsEmpty(cell) Then

RangeValueCount = RangeValueCount + 1

End If

Next cell

End Function

Call RangeValueCount(PhasesActive)

If RangeValueCount > 1 Then

msg = "There appears to be multiple phases selected. Please select only" & vbNewLine

msg = msg & "one phase at a time"

MsgBox msg

End If

View 9 Replies
View Related
Jul 15, 2008

i have a sheet with many formulas on it some in a range are if statements which output a blank ("") if the condition is not met i.e. false.

i need to be able to count how many blanks (false) in the range

View 9 Replies
View Related
Feb 25, 2010

I want to count blanks in a horizontal range (all in one row) that will change (dynamic range). The values in the range could be numbers or words. Some values may be added to the end, but there may still be some empty cells to the right of the last value. My goal is to count blanks in the range up to the last entered value, but no beyond that. As an example:

A6 = 2

B6 = empty cell

C6 = 2

D6 = empty cell

E6 = tt

F6 = empty cell

The range for the count blanks would be A6:E6. F6 is not included because the last entered value is in cell E6.

The answer (count blanks in dynamic range) should be 2.

I have got these 3 formulas to work, but it seems that there must be a better (shorter, faster calculating, more elegant) formula than these:

=COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))

=COUNTBLANK(OFFSET(A6,,,,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+{1,2,3,4,5,6})))

=COUNTBLANK(A6:INDEX(A6:F6,MATCH(9.9E+307,MATCH(A6:F6,A6:F6)+TRANSPOSE(ROW(INDIRECT(""1:""&COLUMNS(A6:F6)))))))

View 9 Replies
View Related
Sep 22, 2009

Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has

ACLU0403598

ACLU0403598

ACLU0403598

ACLU0406600

ACLU5165518

ACLU0406581

ACLU0406581

All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.

View 6 Replies
View Related
Mar 15, 2013

Assume this Data in a spreadsheet

I want to count blank "PI" based on "Product name" & "Product date" occurence together

So I created intermediate field "IS Blank", and I dunno what formula can give me the below results

My obective to get this result

CountBlank for PI = 3

--------------------------------------------------------------------------------

Product Name..... Product Date.... PI...... IsBlank "PI" [Desired Formula output]

xxx .......................ddd ...............Blank.................. 1

xxx .......................ddd ...............Blank.................. 0 (counted above for same xxx&ddd)

xxx .......................ddd222 ..........Blank.................. 1 (PD changed to ddd222)

yyy ......................ttt............... Blank....................1 (another product,yyy)

yyy .....................ttt ...............Blank......................0 (same product and date, so not counting again)

View 9 Replies
View Related
Nov 25, 2009

I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.

View 3 Replies
View Related
May 13, 2013

I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.

I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?

The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.

View 5 Replies
View Related
Feb 7, 2012

I am trying to count different values in a range of cells. I tried a countif function and it worked kind of. Here's what i have.

Column B2 has values j3265,j4463,k5532,y2235,k2334....

I want to count all the J's, K's, and Y's separately.... >=4000 and how many

View 3 Replies
View Related
Dec 9, 2013

In an employee attendance file I am trying to count the number of times an employee has taken 3 or more days of leave together (continuously) in a month. My attendance file looks something like this

sat

sun

mon

tue

wed

thus

fri

[Code] ..........

In the example above E001 has taken 3 continuous days leave twice so formula should return 2, for E002 & E003 the answer would be 1 each.

View 4 Replies
View Related
Mar 3, 2014

In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.

Example: CT11:CT14 looks like

5x20

6x4

5x20

5x8

and so on and so forth..

I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.

View 4 Replies
View Related
Jan 30, 2014

I need to be able to count all the blanks in columns other than A but only until the last used cell in column A. I am using a formula right now that counts the blanks in column A until the last used cell but I don't know how to apply the range of column A to other columns like B and C. Here is an example of what I hope to accomplish:

Formula used in A1 that I need applied to other columns but with the range of column A

="Total Blanks: "&COUNTIF(INDEX(A2:A8,MATCH(TRUE,A2:A8<>"",0)):INDEX(A2:A8,MATCH(2,1/(A2:A8<>""))),"")

Here is an example of what B1 and C1, with the formula, would look like if it counted blanks but with the range of column A

Total Blanks: 3

Total Blanks: 6

Total Blanks: 2

[Code].....

View 2 Replies
View Related
Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

View 3 Replies
View Related
Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is

{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))

+

SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3

All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

View 2 Replies
View Related
Mar 11, 2014

I have a spreadsheet that has the following

I have 30 teams with 15 players each

I have teams in rows and players columns with their ID and then name so B2 would be 444 and B3 would be Johnson and B4 would be 429 and B5 would be Smith

What I want to do is run a formula that counts the duplicate ID's not names and then shows them in a list like below

Duplicate ID, Number of times in worksheet

444 , 23

411 , 21

211 , 15

How I can do this.

View 3 Replies
View Related