Unique Value Count In VBA

Jul 15, 2009

I have this formula that works perfectly fine for what I am trying to do (from another post) but I am wanting to transfer this into VBA. It is an array formula so I'm not sure if this can be done.

How can I convert this, into R1C1 code or any way possible. I would put it within a WITH RANGE statement.

View 9 Replies


ADVERTISEMENT

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

VBA Unique Value Count

Apr 1, 2009

I have the below code, in a cell but I am trying to input this into a macro, so the only thing that shows in the cell is the formula.

The formula I have in the cell is:

View 14 Replies View Related

Count Unique

Apr 25, 2009

aaa.xls

how to count lines in the same order

for exp for this attachment file is 7 lines

View 13 Replies View Related

Formula For Unique Count

Jan 20, 2013

I am attaching the sheet : Uniquecount.xls

Need to do this using formulas and without needing to add any new columns.

Need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab.

Want the formula to be applied to the summary to go and lookup/count from the other sheet.

Have shown the expected results in the col "total unique heads".

View 1 Replies View Related

Formula For Count Unique Value

Feb 27, 2013

Any formula to count only unique values in particular column

E.g.

1
2
3
1
5
1
6
1

answer should be 5

View 3 Replies View Related

How To Count Unique Rows

Apr 7, 2014

Col A Col B
163401 1
163401 1
163401 0
163402 1
163402 0
163402 0

GOAL -I want to be able to count Col B only once for the same set of records in COL A.

Based on Distinct criteria on COL A, I need to be able to count COL B. The count should be 1 for 163401 and not 2. Similarly for 163402 the count should be 1

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

Pivot - Unique Count

Jun 14, 2009

I want to have a unique count field in my pivot table. I already read (via forum search) that this can only be achieved by adding an extra column to the database.

The problem is: I have no clue which formula to use for this column. Please have a look at the attached picture. Can someone provide me with a formula for this example?

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

Count Unique With Two Variables

Apr 23, 2009

I am trying to find a formula that will count the number of unique occurences with two variables (Department and Period).

Since a person can be listed in the department more than once for each period, I need to find the unique number for each department by period. I have attached a simple file as an example. Any formula would go in the yellow area.

View 2 Replies View Related

3 Variable Unique Count

May 20, 2009

I am trying to find the number of unique 3 variable combinations there are in my rows of data. I have tried the frequency function but must be missing something.

Attached is a sample of what I am trying to achieve although there may be a better way of portraying the data.

View 4 Replies View Related

Count Unique Formula ?

Nov 30, 2006

I have a column with names and I would like to count how many names there are in this columns. This is easy but in fact if there is twice the same name the formula will count it twice and I don't want that. Is there a solution to count only the number of persons present in this column without counting twice the same person??

View 12 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 THE UNIQUE NUMBERS

Feb 8, 2007

I have a column full of Invoice No's. I want to count the number of Invoices that are unique, ir avoiding duplicates.

I know I can use the Advanced filter facility but I'd rather do it in a formula if possible.

View 9 Replies View Related

Count Unique Numbers ..

May 4, 2007

I'm working in Excel 97, trying to use a formula I found in the archives here to count unique numbers in a column.

=SUMPRODUCT((V2:V10000"")/COUNTIF(V2:V10000,V2:V10000&""))

I'm not exactly sure what this formula is doing, but it seems to work reasonably well, except that it's adding one to my total.

i.e. I have a total of 15 and it returns a value of 16 or I have 0 and it gives me 1.

View 9 Replies View Related

Unique Count When Subtotaling

Sep 19, 2007

I need to create subtotals summing an amount column and then also create a unique count of documents in a different column but on the same subtotal line. Currently, I am running the subtotal on the amount but then manually inserting the following formula on each subtotal line to obtain the unique count.

=sum(if(frequency(range,same range)>0,1))

Is there a way to automatically recieved a unique count when subtotaling without me manually entering this formula numerous times throughout my spreadsheet.

View 10 Replies View Related

Unique Count Condition

Jun 26, 2008

I need a unique count of the ID number. What I need is to check and see if the row has a number in it and if it is greater than 0 and is not blank. If the row has 0 in it, then that is not counted in the count. For in stance .....

View 9 Replies View Related

Is There A Way To Get A Unique Count In A Pivot

Dec 4, 2008

I have a pivot that shows Patients, Invoices and charges. I want to throw Patient, Inv # and charge amount in the row area and put PatientId in the Page area as a count. It works fine except it double counts patients with more than one invoice...I want a unique patient count.

View 9 Replies View Related

Count Of Unique List

Jan 22, 2009

to calculate the count of unique text values from a list based on condition.

I have 2 columns A & B. In column A, I have Yes or NO text and in column B, I have Client names.

I want to count a list of unique values from column B if it is Yes in column A.

I have to write a formula to do this task and will appreciate any expert help in solving this.

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

Jun 16, 2009

Counting unique items in column (numbers)

View 9 Replies View Related

Count By Unique Entries

Feb 24, 2007

I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'.

I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column.

An example,

[Name].....................[Reason]
C7 ...........................E7
John Potts.................Truancy 2
Matt Jones................Truancy 10
John Potts.................Truancy 4
Matt Jones ...............Late AM
Pete Burns................Late PM
Pete Burns ...............Late Both
Steve Lopez..............Truancy 6

Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once]

Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once].

I have also included a sample workbook.

View 9 Replies View Related

Unique Count By Criteria

Mar 28, 2008

i've tried to locate a matching post for my query, although a couple have been close I havn't been able to find one which matches what i'm trying to do. I have a spreadsheet which has two columns A:PersonIds and B:Month/Year. A person id can appear many times for a given month. What I am wanting to do is to perform a unique count of person id's by month. Take the following example:

ID , Month/Year
001, Jan-08
001, Jan-08
002, Jan-08
002, Feb-08
003, Feb-08
001, Mar-08

Below is what I expect the count to total

Totals
Jan-08 = 2
Feb-08 = 2
Mar-08 = 1

I've also attached an excel file with what i've just outlined. Is there a way to do this without using VBA, but with a combination of array's and nested if statements?

View 5 Replies View Related

Layout & Count By Unique ID

Apr 9, 2008

I have the following data: What I am trying to do is arrange the data such that in another sheet I have all the unique Task IDs and the associated names such that the resulting table would look like: In essence, each person may have more than one task ID that might be repeated in a set of data. The objective is to sort by name, assigned task ID, and the number of times it has shown up in a set of data.

View 2 Replies View Related

Excel Count By Unique Dates

Nov 28, 2012

I have an excel file with 1723 entries. One of the fields is DATE. I have it sorted by DATE. I need to count the number of entries per unique DATE.

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

CountIfs - Get Count Of All Unique Fields

Dec 17, 2013

Column A Column B
2012 20
2012 30
2013 50
2013 30
2013 50
2014 10

I've got two columns like this. I need to see if a countifs() would handle it for me, or if there's another formula to use.

I need to get a count of all fields in column B that are unique and are 2013 in Column A. In this case the answer would be 2 (in 2013 there was a 30 and a 50, duplicates are skipped).

View 6 Replies View Related

Count How Many Unique Users Are Using Tool?

Jan 21, 2014

I have 2 sheets, one with users and a tool they have been using, and one with a list of unique users, I would like to count how many unique users are using a tool. The sheets are set up like so,

Sheet 1
User Tool (number of uses)
abcdefg 1
hijklmn 5
1234567 1
1234567, abcdefg, opqurst 2
wxyz123 0

Sheet 2 Have they used the tool

abcdefg
hijklmn
1234567
wxyz123
opqurst

So yes, I am trying to count whether or not the unique users have used the tool, this is a simplifed version as my data set runs into the thousands. I was using the countifs function, but that doesnt count properly if theres more than one user in the group.

View 4 Replies View Related







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