Count Occurrences Based On Unique Values In Another Column

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

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?

I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.

Col A ABCD ABCD ABCE BDCE BDCF ZXYE ZXYE YEZY CDEF

Col B is where i would like to list all the unquie vaules with a count e.g. ABCD 2 ABCE 1 BDCE 1 BDCF 1 ZXYE 2 YEZY 1 CDEF 1

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.

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

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.

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

I get this work a lot and am looking for a much more effective way of working the dataset,

I have numbers like 1.1, 1.2, 1.3 and so on....... These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....

I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.

I am attaching a sample document to see how it is laid out. [URL] .....

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

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.

Second Tab Account Name Account Number Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

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.

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?

I have a worksheet with Coulmn A and B , wherein , Column A has a list of values that can be duplicated. Based on the values in Column A, I want to fill corresponding cells in Column B. see the attached.

In need checking if a particular ID number is repeated more than once in column B. I need to write a formula in each cells of "No.of Repetition" column or Column C to check if respective ID number in column B is repeated more than once and display the count or display a condition true or false.

I have a workbook with about 10000 rows of data for about 100 suppliers in Sheet1 and about 15000 rows of same Suppliers payment details in sheet2.

What I am doing now is:-Filter every supplier names in Column A of sheet1, copy all the rows and paste in sheet1 of a new workook & Again copy the Payment details of a same supplier from master file sheet2 and paste the same in sheet2 of this new workbook then save the files with the supplier name as file name in my documents folder code, that filters each suppliers data from sheet1 & 2 and paste in new workbook sheet1 & 2.

I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.

I have my workbook set up so that one worksheet contains the names of all my departments/sections listed with their 3 letter code (this can be anything from AAA-ZZZ, e.g 'FYH', 'JWK' or 'KZA' are all valid) in Column F. I then have Columns H, I, J & K blank and require them to be auto-filling based on data from 4 other worksheets - i'll detail what i need below, but Column H needs to be filled based on what it finds in worksheet 'Data 1', Column I should be filled based on what it finds in worksheet 'Data 2' etc. etc. I have four further worksheets that contain data based on the types of assets owned by the department/section, these are 'Data 1', 'Data 2', 'Data 3' and 'Data 4' and I use these as described above, with Column H referring only to 'Data 1', Column I only to 'Data 2', Column J only to 'Data 3' and Column K only to 'Data 4'.

What i need then, is a VBA that will take the value of a cell in column F on my main worksheet (called 'main') and then count the number of occurences of that value in Coulumn A on another worksheet and place the number of occurences into Column H, I, J or K based on which sheet it's checking.

For example, Cell 'F2' on my main worksheet contains the value "ABC". I need to take "ABC" and search Column A of worksheet "Data 1" to find how many time it occurs, placing the result into Column H of worksheet 'Main', I then need the same value to be counted in Column A of 'Data 2' and its result placed into Column I on 'Main', and again for Column A of 'Data 3' with the result being put into Column J of 'Main', and finally Column A of 'Data 4' to be checked and that result placed into Column K on 'Main'.

does this seem too much of a task for VBA? or am i best doing each column separately with a CountIf or Array formula?? Not sure whether a COUNTIF or Array formula will do the job.

I have my workbook set up so that one worksheet contains the names of all my departments/sections listed with their 3 letter code (this can be anything from AAA-ZZZ, e.g 'FYH', 'JWK' or 'KZA' are all valid) in Column F. I then have Columns H, I, J & K blank and require them to be auto-filling based on data from 4 other worksheets - i'll detail what i need below, but Column H needs to be filled based on what it finds in worksheet 'Data 1', Column I should be filled based on what it finds in worksheet 'Data 2' etc. etc. I have four further worksheets that contain data based on the types of assets owned by the department/section, these are 'Data 1', 'Data 2', 'Data 3' and 'Data 4' and I use these as described above, with Column H referring only to 'Data 1', Column I only to 'Data 2', Column J only to 'Data 3' and Column K only to 'Data 4'.

What i need then, is a VBA that will take the value of a cell in column F on my main worksheet (called 'main') and then count the number of occurences of that value in Coulumn A on another worksheet and place the number of occurences into Column H, I, J or K based on which sheet it's checking.

For example, Cell 'F2' on my main worksheet contains the value "ABC". I need to take "ABC" and search Column A of worksheet "Data 1" to find how many time it occurs, placing the result into Column H of worksheet 'Main', I then need the same value to be counted in Column A of 'Data 2' and its result placed into Column I on 'Main', and again for Column A of 'Data 3' with the result being put into Column J of 'Main', and finally Column A of 'Data 4' to be checked and that result placed into Column K on 'Main'.

does this seem too much of a task for VBA? or am i best doing each column separately with a CountIf or Array formula?

I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.

What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.

A simple table example would be: colA colB colC Dan Complex Build House Dan Complex Build Shopping center Dan Simple Take dog to walk Jose Simple Clean desk Maria Medium Paint office roof

Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex")) But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...

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 ?

I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.

Been working on this issue for the last couple of days with no luck. I have a column of data that is a data and time stamp, for any date or time like below. This is an ever growing list as users continue to populate the data.

On a separate sheet I have a list of times by 15 minute increments starting at midnight until 11:59PM like below.

12:00:00 AM 12:15:00 AM 12:30:00 AM 12:45:00 AM

I'm trying to count how often a time stamp occurred between two 15 minute increments. ventually I'll be plotting this data on a graph to show where most occurrences happen.