# Count Unique Cells Based On Adjacent Column

Jan 3, 2008
I have a worksheet with following data in 1 tab.

[TABLE]

Seq Class

SE110

SE270

SE110

SE370

SE310

SE110

SE170

SE370

[/TABLE]

In a different tab I want to calculate the unique count of sequence for a specified class.

In above eg: for class 10 it should return 2

View 3 Replies
ADVERTISEMENT
Jul 1, 2009

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

View 4 Replies
View Related
Mar 18, 2013

I've got two columns, one with policy numbers, the other with error codes.

A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function

=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))

I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with

=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))

The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.

What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?

Here's my sample data: [URL]

View 9 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
Apr 30, 2014

In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.

Capture.PNG

At the moment I have a formula as below:

=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))

this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.

Modifying my formula. I have attached the sample workbook below.

Book1.xlsx

View 7 Replies
View Related
Jan 4, 2008

How would you select cell values from differing rows based on the name in an adjacent column. ie, if it says John, add that data to the source data, if not move on.

View 5 Replies
View Related
Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

View 8 Replies
View Related
Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:

Column C Column D

111111 M

111111 M

111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):

="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

View 9 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
Nov 23, 2011

Is there a way to do this without using a macro, but I need it to be in a macro.

Column A has a value I am calling a label, ex. ABCDEF which occurs over and over. Column B has a list of animals, many of which repeat AND will be together if they do repeat. In other words, all rows in Column B with Cows are together, occurring in consecutive rows. I need a macro that will look at each row in column C and increment +1 starting at 0. That will be concatenated with the value in Column A and pasted as a value in column C.

See the linked spreadsheet tabs for Before Macro and how it should look After Macro is run.

[URL] ........

View 1 Replies
View Related
Mar 10, 2014

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

View 1 Replies
View Related
Apr 24, 2009

I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:

AB

1-May

1-May777

1-May

1-May

1-May

2-May999

3-May

2-May

1-May

2-May111

1-May

If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.

View 2 Replies
View Related
Feb 3, 2014

In the following spreadsheet, I'm trying to automatically count the amount of times an A code (A1 or A2) or B code (B1 or B2) appear under each person's name in the K1:M5 table.

View 3 Replies
View Related
Mar 10, 2014

count cells row wise that meets criteria both in column & Row as well.

View 2 Replies
View Related
Aug 11, 2014

I want to count the number of times partial text strings occur in a cell adjacent to another cell containing specific text.

A B

FB Milton v Town PHOTO

BS Fairfax v South

BS North v Town

BS Milton v South PHOTO

FB North v Milton PHOTO

FB Milton v South

I'm looking for the number of times "FB" and "Milton" occur in Column A when "PHOTO" also appears in Column B in the same row. From numerous searches I've tried figuring out VLOOKUP, DCOUNTA, etc.

View 6 Replies
View Related
May 8, 2008

I am attaching a sheet in which Data can be entered in cells C5 & D5 in "Calculation " sheet, depending upon the value of C5 it will count all the Occurrence of data of D5 in adjacent cell i.e. (one up & and one below of data of C5) . data will search in "PCM_PLAN" sheet in column " I ".

count will be given in E5 in "calculation sheet"

View 9 Replies
View Related
Apr 14, 2014

I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.

View 2 Replies
View Related
Jun 1, 2007

I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.

Situation:

I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this

jp RET

jp RTO

jb RET

ma RTO

Rn CI

ma Fur

tc CI

ma RET

jp RET

and I want to have a count of how many RET's there are for Initials JP ect

View 6 Replies
View Related
Jun 23, 2008

I want to count no of "yes" in Range Column B only if corresponding values in column A is less than 10/06/2008

Please see the attached file for refrence

View 5 Replies
View Related
Jan 19, 2008

One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.

View 4 Replies
View Related
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
Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed

Disposal Order

Latest Decision date for D.O.

06/05/2014

D.O.001

[Code] ........

View 6 Replies
View Related
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
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
Jun 5, 2014

I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:

0001: Apple

0001: Apple

0002: Google

0003: Sony

0003: Sony

0003: Sony

0003: Sony

0004: Ford

0004: Ford

0004: Ford

.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.

View 1 Replies
View Related
Jan 29, 2012

I am trying to get an unique list based on the value being the only member of the group.

Sheet1

A B 1 Group A A 2 Group A C 3 Group B B 4 Group C C 5 Group C B 6 Group D B 7 Group E C 8 Group E B

The solution is

Sheet1

D E 2 Solution 3 Group B B 4 Group D B

Is there a way to do this?

View 9 Replies
View Related
Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies
View Related
Feb 19, 2009

I'm facing a big problem and I can't found a solution for days.

I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

Let me Put to you an example for you been able to understand my problem.

Example considering Range "C2:C20" exluding blank cells

(1) - To count unique values

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

(2) - To count unique values based on criteria in range "B2:B20"

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))

(3) - To sum unique values on "E2:E20" based on same criteria

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))

They all work fine. But I need to insert a merge at Row 10 for

better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.

I just don't know what to do guys ....

View 9 Replies
View Related
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
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