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


ADVERTISEMENT

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

Breakdown Number Of Part Matches

Jan 29, 2008

I was given this new task this year. Normally this process is done by hand, by one of the managers, but since it now belongs to me, I look at it as being a little to tedious to do. So I would rather have excel do it for me.

We have about 5 CSRs who handle clients based on the first letter in the clients name. I guess to make this easier to update in the future if the macro would respond to a cell reference for the number of CSRs that would be great. I will need to perform this task Annually, and of course each time a CSR leaves or we hire another one. This way work loads are kept close to the same.

I have a list of letters in column A. Each of these letters has a corresponding number of clients in column B. I have been given the task to try to split these values equally between the 5 individuals. I know that since the numbers dont all add up and divide equally it cant be done easily by hand.

I have attached an excel file with what I have in the sheet. Sheet 2 shows what I would like to have as an example as output on the same sheet. (What I put on the sheet is an example of the way I want it to look, are not close to adding up to a "close to equal" total.)

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

Total Number Of Unique Matches In Two Columns

Nov 11, 2008

Column A = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison

Column B = numerical IDs, multiple entries, may have duplicates that should only be counted once in comparison

Need: 1 cell that compares all of the A range to all of the B range and returns a a percentage of repeats. So, if there are 50 entries in A, and only 40 are unique, and B includes 10 repeats (unique, not multiple), then the returned value would be 25%.

View 6 Replies View Related

Count Unique Values Of Defined Cell Between Two Dates

Jul 26, 2013

I have a group of employees who work different divisions and work is not done everyday. I want to count the unique values of a division (only once) per day that the division shows up. Within a week, workers in a division might work 3 days and not other days. I've tried a few array formulas but they count unique within a range and I need the unique value to be based on the selected cell.

Please see attached : Unique Values In Between Dates.xlsx

View 4 Replies View Related

Count Unique Values If Adjacent Cell Meets Criteria

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

Count Unique Text Values (number With Text And Quotation Mark) With Formula

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

SUMProduct (182 Matches Part 1 Of Which 32 Match In Part 2)

Nov 18, 2008

Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).

=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))

View 3 Replies View Related

Count All Unique Values (text) + Definition Of UNIQUE :D

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

Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

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

Count Unique Values In Column Based On Values In Other Columns

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

Count The Number Of Multiple Values In A Single Cell

Jan 28, 2010

formula that will return the number of "x"s or "o"s within the same cell.

The cell has values that are formatted in multiple ways for example: PXX--XXP, --XO, OXX--.

I want the formula to return 4 if the cells has PXX--XXP or 3 if the cells has --OXX etc.

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

How To Count Only Unique Values

Jul 24, 2014

I'm working with a data set (attached) that tracks financials at both the job and item level (in my customer's data - a unique job can be made up of a single or multiple items).

I've successfully built a formula (in column C of the attached) that counts the total number of items and their associated $ values that were invoiced in a given month based on my customer's requested item-type categorizations (i.e. "Exact Match", "Similar", "Inferred", "Cost-Plus").

The problem I'm running into is building a formula that counts the first unique instance of the Job ID associated with those items. For example - in cell C33 on the attached - The total number of items invoiced in June that were "Exact Match" was 10. These items were spread across 9 unique jobs - and that's where I'm struggling - to build a formula that only counts the first unique instance of the corresponding Job ID in Column C.

The formula I'm having trouble with is located in Cell D12 on the "Dashboard" tab of the attached.

The formula as I have it is returning a result of 8 - because it's summing the "1"'s in Column I on my "Invoice Data" tab - however it should read 9 (Filter Column Q on the "Invoice Data" tab to "June", Filter Column AQ on the "Invoice Data" tab to "Exact Match", and then sum the unique job IDs in Column C).

Excel_Forum_Example_Dataset_7.24.14.xlsx‎

View 1 Replies View Related

Count Unique Values ....

Dec 28, 2009

So here is my dilemma and I haven't been able to find anything to exacly help me out:

I have a large (100k+ records) sheet of sales invoice details.

I need to add a total item line per invoice number to each record ....

View 14 Replies View Related

Count Unique Values

Jan 13, 2010

Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274

I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.

View 14 Replies View Related

How To Count Only Unique Values

Jun 6, 2014

I want to count data that corresponds to 4 criteria for data located in Columns B, C, D and E. However, I only want to count only the unique values in Column A. How do I improve on this formula?

=SUMPRODUCT(($E$2:$E$5=J$2)*($D$2:$D$5=I$2)*($C$2:$C$5=H$2)*($B$2:$B$5=G$2))

See data below

View 4 Replies View Related

Count All Unique Values

Apr 15, 2009

Is there a formula that can be used to count all unique values in a specified column.
Example:
Col. A
123
123
Test
ABC
ABC
ABC

Result: 3 (since there are 3 unique values in col. a)

View 9 Replies View Related

Count Unique Values, For Specific Values

Sep 13, 2006

I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))

Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.

View 4 Replies View Related

Countif To Find Number Of Unique Values And Number Of Times Repeats Happen

Aug 20, 2013

I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.

I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.

View 9 Replies View Related

Cross Check 2 Ranges For Matches & Return Row Number Of Matches

Apr 29, 2009

I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK

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

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

Find And Count Unique Values

Jun 24, 2009

I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.

View 8 Replies View Related

Count Unique And Specific Values

Aug 18, 2011

Having a large spreadsheet with Columns A to F filled with data.

I'm trying to create a formula that's count the unique value "Order no." (Coloumn F) but only the ones between certain Departments Column(H) a certain "Error type" in Column(J).

View 8 Replies View Related

Count Unique Values Between Two Dates?

Nov 19, 2013

I want to count the unique values between 2 dates. He only needs to count the values that match 2 criteria. I want to know how many unique orders were place between 01/10/2012 and 30/09/2013.

In my Data sheet:
Colomn X: Customer name
Colomn D: Status
Colomn AH: Date of order (Day/Month/Year)

I want to count the unique values in Colomn O: Ordernumbers

This is what I already have:

=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4;IF(Data!$D:$D="BOOKED";Data!$O:$O));Data!$O:$O)>0))

This formula is working. He counts the unique values in colomn O that meet the 2 criteria (Status = "BOOKED"; Customer = A4).

But I don't know how the add the date range in this formula. I want him to take only the values that are between 01/10/2012 and 30/09/2013.

View 9 Replies View Related

Count Unique Values With Critera

Jan 28, 2010

I have a large list of data and need to filter unique values of 1 column with a criteria.

For example:

A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4

I want to filter out only the lines whereby the column A & B is the same comment/value.

Other words: both column A & B have to be unique values.

Based on the list above this means that the list should look like this:

A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4

View 10 Replies View Related







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