Sumproduct: Determine The Number Of Unique Names In Column

Jan 22, 2010

I have 1500+customer names in column C7 of table. I'm looking for a SUMPRODUCT formula that achieves the following 2 logical goals. First, it needs to determine the number of unique names in column C7. It can't just count the rows because many customers appear multiple times in the range. So, I have been using the following formula and it works great:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&"")))

Second, I'd like the formula to tell me how many of the unique names in C7 correspond to public companies. Column C8 of my table indicates whether or not a company is public ("Yes") or private ("No"). So, I've been using the following formula and it seems to deliver the right result:

=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&""))
*
(COUNTIFS(Table[C7],Table[C7]&"",Table[C8],"Yes")>=1))

But the added COUNTIFS statement seems very unwieldy and indirect to me. Why do I have to examine every instance of a particular company's name when the answer could be determined by looking at any instance of the name -- after all, each company in C7 is either public or private, and has the same corresponding value in C8 (whether "Yes" or "No") everywhere that its name appears in C7!

So that's my first question: is there a clearer, more efficient way to achieve the same result? My second question has to do with toggling the public company test.
What if I create a special cell, say A1, that will always contain either the word "On" or "Off"........

View 4 Replies


ADVERTISEMENT

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

Sumproduct -in Column A I Have Dates And In Column B I Have Names

Jan 30, 2008

in column a I have dates and in column b I have names.

eg

A1 = 1/1/08
A2 = 2/3/08
A3 = 3/1/08
A4 = 3/1/08

B1 = Jenny
B2 = Jenny
B3 = Jenny
B4 = Pat

I am trying to count the number of instances of "Jenny" in January.

I tried =sumproduct(A:A,>=39448

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

Allocating Employee Names With Unique Reference Number

Oct 31, 2008

I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.

The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.

I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.

as there are 600 names and some can appear up to 4 - 5 times!

View 9 Replies View Related

Extract Unique Names From Column

Jun 18, 2007

I have a column which has about 600 Names, and some of them are repeated, so I want to extract the Unique of them into a separate column, How can I do that?

View 5 Replies View Related

Determine Row Number Of Last Value In Column

May 15, 2009

Trying to do a simple workbook here, with hopefully no macros/VB script.

Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.

The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.

The cell I'm trying to create is part of a 'stats' section.

View 7 Replies View Related

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

View 26 Replies View Related

Count Number Of Times Names Appear In Column?

Jan 6, 2014

I have a list of names all in one column, separated by row...

Example...

John Doe
John Doe
Jane Doe
Jane Doe
Jane Doe
James Jones
James Jones

I want to count how many times each name appears. Like this:

John Doe | 2
Jane Doe | 3
James Jones | 2

This is a very large list of names and I prefer not to have to type each single name into a formula because there are hundreds of separate names.

View 4 Replies View Related

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

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

Calculating The Number Of Unique Entries In A Column

Jul 17, 2009

how to calculate the number of unique entries in a column? I have a column of email addresses and i would like to know how many of them are unique.

View 9 Replies View Related

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

Display Number Of Occurrences For Each Unique Date In Column?

Mar 26, 2013

I have a column of dates (Column A) with intermittent blanks and I need to display the number of occurrences for each unique date.

This is what I am starting with: (Note= The number of rows varies by project so I need the whole Column A referenced)

Dates
02-24-2013
02-24-2013
12-15-2012

[Code].....

View 9 Replies View Related

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.

View 9 Replies View Related

Formula To Calculate Number Of Unique Values In A Column With Filters

Jul 9, 2014

I have a file with a column containing a series of records where a reference number can be repeated several times. I want to create a formula that will count the number of unique reference numbers in the column.

However - and this is the tricky part - I need it to allow for when the report is filtered, i.e. something like a SUBTOTAL function which ignores the hidden values.

So, the column in the full report has 691 unique values across 2,200+ records. If I apply a filter the column only has say 78 unique values. Is there a formula that can calculate this?

View 12 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

Move Data In Column To Single Row For Each Unique OSIS Number

Mar 12, 2014

I need to move data in column E to single row (as text string is fine as I can Text to column later) for each unique OSIS number. VB should copy columns A-D to sheet 2 then move each row data for unique OSIS to column E as one long text string separated by common. I can then parse out using text-to-column. Below is the VB code which stops on: txt = txt & Sheet1.Range("E" & x) & ","

I want to be able to look up class code and score by OSIS either using vlookup or in Access with OSIS as primary key.

Public Sub processR2C()
pos = 1
txt = ""
For x = 2 To 99999

txt = txt & Sheet1.Range("E" & x) & ","
If Sheet1.Range("A" & x) Sheet1.Range("A" & x + 1) Then
Sheet2.Range("A" & pos) = Sheet1.Range("A" & x)

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

Here is sample data

A B C D E

229647698 ABBASI AMJAD 2L4 ESS21QQR,40

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

View 4 Replies View Related

Combine Multiple Records Onto 1 Row Based On Unique Number In Column

May 2, 2008

I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.

What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:

BEFORE
HH Acct
1 1234
1 2345
1 3456
1 4567
2 9876
2 8765
3 1113
4 5556
4 4447
4 3335

AFTER
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
3 1113
4 5556 4447 3335

View 3 Replies View Related

Determine Sheet Names Starting With X Specific Characters

Jun 12, 2008

I have a worksheet that will have tabs added as time goes by and a macro within the workbook currently selects all of the sheets and copies and pastes formulas within all of the sheets simultaneously. Right now, if a tab is added, the macro needs to be updated to select that specific tab name as well.

I would like to simply have the macro select all of the sheets starting with "IL" until the sheet before "reference", or all sheets except the first 3 and the last 2 if that is easier. Any ideas? And thank you for your help, you people really amaze me sometimes!

View 9 Replies View Related

Excel 2010 :: Generate 6 Digit Unique Random Number For ID Column A

Jul 18, 2012

How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.

View 9 Replies View Related

Determine If Date Falls Within A Range For Multiple Unique Lines

May 19, 2014

File A has patients with discharge dates over two years. Each patient may have multiple dates. Example:

Patient Discharge Date
John Smith 7/1/2012
John Smith 10/1/2012
Judge Judy 7/1/2013
Judge Judy 12/1/2013

File B has office visit dates for the patients. Again, each patient has multiple office visits. I need to be able to see which office visits were within 15 days of a discharge date. Example:

Patient Office Visit
John Smith 6/1/2012
John Smith 6/15/2012
John Smith 7/10/2012
Judge Judy 7/20/2013
Judge Judy 12/12/2013

Is there any way to note next to the discharge date that for John Smith the 7/10/2012 office visit was within 15 days of the 7/1/2012 discharge? And the same for Judy with the 12/12/2013 visit?

View 8 Replies View Related

Sumproduct And Unique Fields

Feb 5, 2008

I have a sample table below to illustrate my problem, the numbers in red are what I'm looking for a formula to calculate, probably a sumproduct, although I'm open to any suggestions.

Weight through conduits are to be summed only once if they are repeated in any particular month. (DEF1000 is in building 2 and 5 in March, but its weight should only be summed once) I hope this makes sense.

View 9 Replies View Related

Sumproduct Count Range Of Names

Nov 17, 2011

What is the formula for sumproduct to count a say column c for a range of names and as long it match column k for yes.

View 1 Replies View Related

Sumproduct Trying To Match Cells With Slightly Different Names

Jun 10, 2013

See attached spreadsheet. I have 2 tabs. The current portfolio tab is feeding in from the computation tab. My formula in column C works for 2 securities, MSFT, and AApl as the name is identical in both tabs. I am running into an issue where the names are not identical.

Example: I want to sum both the INTC, and the INTC pref in the computation tab and have that fill in the current portfolio tab. Also, in the computation tab ticker 005930 ks should fill in the current portolio tab for samsung electronics. My lasst issue is in the computation tab where I want to pull in sony, and sony adr (multiplied by 1,500). total sum 1,5723,995,015 in the current portfolio for sony.

excel question.xlsx

View 9 Replies View Related

Create A List Of Unique Names From A List Of Multiple Names

Oct 21, 2009

I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.

What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.

The code I have so far is below:

Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant

In my mind it should check the names in the unique list against the imported list and add any missing names.

View 9 Replies View Related

Excel 2010 :: Sumproduct Of Unique Values In Multiple Ranges

Jul 31, 2014

I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

View 9 Replies View Related

List Unique Names

Oct 24, 2008

I have a column of names in Column D

I need code to list these names in column G

when I hit a button.

View 9 Replies View Related

Formulas To See If Names In One Column Exists On The Names In Second Column

Aug 18, 2014

I have two columns listing about 400 and 500 names respectively.

First Column has names in alphabetical order and the second column is random.

One column has names in this format: Last name, First name

And the other column lists the names as first name and then last name without the comma

I am trying to see if the list of names in one column exists in the other.

Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?

Name Lists (1).xlsx

View 14 Replies View Related

Count Per Unique Dates And Names

Jul 9, 2009

The question is presented and explained within the attached WB.

View 4 Replies View Related







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