Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










How To Count Top 10 Values


I couldn't find the right formula for this... I'd like to sum top 10 values from a range of values in cells. Currently only way I can think of is to sort all the values first and then count the top 10 cells. But is there a way to find the top values with a automatic formula, which would fit to one cell?

Example:

A
4
6
8
3
13
4
16
11
9
2
8
8
10
5

Sum from top 10 values would be 96... But how to reach this with a formula?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Count Intervals Of 2 Numeric Values In Same Row And Return Count Across Row
I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.

The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83

View Replies!   View Related
Count Unique Values, For Specific Values
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 Replies!   View Related
Count Some Values But Not Others
I am trying to count the number of the cells below EXCEPT when the value equals "OFF". So the answer should be 6 (8 cells - 2 OFF's). Would anyone know how to make a formula that would do that?

$0.10 BALANCEDOFFOFFBALANCEDBALANCEDBALANCED($1.00)

View Replies!   View Related
Count Values Less Than Zero
I am using and index and match formula to find what date the value is less than zero. However I am not using a continous time series in my figures but a time span for example 2008-01-30 - 2008-02-05. The formula works fine with a continous time series but not for time spans. Does anyone have a clue to get round this. I am enclosing an example where the formula works and also an example where it does not.

View Replies!   View Related
Count And Sum Values
I am trying to enter a formula that will count the number of values in a column and multiply the total by a decimal. For example if A1:A5, contains the following A, A, B, C, B then I need a formula that would multiple the number of occurrences of "A" (2) * .25, the number of occurrences of "B" (2) * .125 and the number of occurrences of "C" (1) *.0625.

View Replies!   View Related
Count Consecutive Values
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?

View Replies!   View Related
Count These Sorted Values
Still i could manage with Sorting of data. Now i have following data

Name Project Artha Artha Prj Artha Artha Prj PBB ACR Seema Brew Seema SEMC Seema handset dev & testing Seema SEMC Seema handset dev & testing Shashi PRJ1 Shashi PRJ1 Surajit FLV Surajit FLV Surajit FLV
And i want to have following result using a macro
Name Project Count Artha Artha Prj 2 PBB ACR 1 Seema Brew 1 Seema handset dev & testing 2 Seema SEMC 2 Shashi PRJ1 2 Surajit FLV 3


View Replies!   View Related
Count Unique Values ....
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 Replies!   View Related
Count IF, Then, Else Statements That Have Values
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

View Replies!   View Related
Count All Unique Values
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 Replies!   View Related
Count Consecutive Values ...
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.

View Replies!   View Related
Code To Count Certain Values
for code that will test cells e3:g3 in sheet 1 of workbook "AR" for a value of "OK" or "Error". These cells contain If formulas that return "OK" or "Error"

If all Cells = "OK" then I need the macro to put "Ok" in D5 in sheet 1 of workbook "Guide". If 1 or more cells = "Error" then put "Error" in D5.

Also, I know I could use a formula in D5, but I really need the code for my specific application.

View Replies!   View Related
Count Non-repeating Values
It has several steps and i will now just explain the least complicated one, and hopefully once i get this done i will figure out the rest myself. If not then i will need some more help

I have 500 entries (names) in column A and each entry has a coresponding category in Column B (Large, Medium, Small).

I want to see how many entries are there for each category, but i need to count the names which appear in the list only once. If the name is entered more than once then i dont need to count it.

I hope i made it clear as i am not able to provide excel table as an example do to security settings.

I am usinng Excel`03

View Replies!   View Related
Count Unique Values
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 Replies!   View Related
Count Values Greater Than Zero
I want to look to a range and if there is a value greater than zero I want to count it. I keep going round in circles trying to do this and now I give up.

View Replies!   View Related
Count Values In Column
I grow my business by dialing a massive amount of leads everyday which I manage in Microsoft Excel. In row "E" I have it labeled as Dispositions and each call I make gets labeled as..

WN = Wrong Number
AM = Answering Machine
UA = Unavailable
NQ = Not Qualified
DNC = Do Not Call
IV = Invite
SA = Sale

I would like to create functions or formulas which displays the total number of each of these dispositions from column E and shows the totals in separate cells so that I dont have to count them manually anymore.

View Replies!   View Related
Count At Least 4 Consecutive Values
I have a range of binary numbers and would like to count the number of times the value of 1 appears BUT there must be at least 4 consecutive 1's.

Here's an example:
1
1
1
1
1
1
0
1
0
1
1
1

The final count should be 6.

View Replies!   View Related
Count The Values Through Sumproduct In VBA
The problem facing by me that I have a worksheet in which I count some values through sumproduct function in vba but its not working but if i manually put in this in sheet it works.here is the code.

Dim Sal As Workbook
Dim rng As Range
Dim rng1 As Range
Dim Dept As Range
Dim Dept1 As Range
Dim rg As Range
Dim i As Byte

Sub salries()
Application.DisplayAlerts = False
On Error Resume Next
Set Con = Workbooks("Branch Wise Deparment Wise No. of Staff.xls")
Set Sal = Workbooks("salarysheet.xls")
Sal.Activate
Sheets("Working").Delete
Sheets("GT").Activate
Range("B3").Select
Set rng = Range(ActiveCell, Selection.End(xlDown))..............

View Replies!   View Related
Count Of Multiple Values
I need a formula that will count all instances of A*, A, B & C in a row D3: Y3

so if

D3....E3....F3....G3....H3.....I3
D......C.....A*....A......E......B

would return 4.

View Replies!   View Related
Count When Values Occur In Different Arrays
My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.

View Replies!   View Related
Find And Count Unique Values
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 Replies!   View Related
Sort The Data :: Count The Zero Values
In a column I have loads of numbers like this except there are 5000 of them.

3
2
0
0
0
0
2
0
3
0
0
2
0
9

how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.

3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9


View Replies!   View Related
Count Two Different Text Values Within Two Columns
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the records
that contain “XX” anyplace witihin Column A plus have “XXXX” in Column B.

View Replies!   View Related
Count Unique Values With Critera
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 Replies!   View Related
Count/Sum Unique Values/Entries
Can DCOUNTA be used to only count unique values?

If the above is not possible how would I do the following:

Criteria

Column H = text1 AND Column B date is between date1 & date2

Count unique values in column C

View Replies!   View Related
Count Values Greater Than Or Equal To Zero
How can I count the number of cells in a range that contain values that are greater than or equal to zero?

View Replies!   View Related
Count Of Unique Values With Duplicates
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

View Replies!   View Related
Formula: Count Distinct Values
I have this formula that works perfectly in Excel. It counts the unique TEXT values in a column. But it does not work in the VBA editor. Count = SUMPRODUCT((C6:C39<>"")/COUNTIF(C6:C39,C6:C39&""))

View Replies!   View Related
Count Consecutive Values By Condition
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.

So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.

Is there a formula I can use in column N for this without doing 12 "if" functions?
I think this needs to be an array formula but the ones I've tried I can't get to work.

View Replies!   View Related
Tally Count Of Values In Column
I have a list of varying IP addresses in a column. I need to create a formula that will tell me how many times an IP address appears in the column so that I can ultimately determine the most common IP address listed.

View Replies!   View Related
Count Combinations Of Values In 2 Columns
Have a monthly report process in which I need to count to combinations of values in 2 columns. Example:

A 1
A 1
A 2
A 3
B 2
B 1
B 3
B 3
B 4

Count results: A&1 = 2, A&2 = 1, etc. I'd like to get a summary table of the results. I see how to use sumproduct to count combinations, but that requires a sumproduct formula for every combination. The 2 colums are lengthy and the contents vary each month, so building the formulas each month will be a fair amount of work. Is there another way?

View Replies!   View Related
Count Number Of Different Values In Duplicates
Below is a range of data i am working with, i am trying to create a formula that will count the number of different entries in column A for each different entry in column B.
ie how many differnt values are there for "Packing"

008003PICKING MISTAKE
008042UNFIT FOR PURPOSE
008035PACKING
008035PACKING
008035PACKING
007960CHANGE OF MIND
007986PACKING
007986PACKING
008050UNFIT FOR PURPOSE
008070CHANGE OF MIND
008070CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008086PACKING
008085PACKING
008085PACKING

View Replies!   View Related
Count Data/Values Greater Than Zero
I have a spreadsheet that contains dates in column A and numbers in column B. For some dates I do not have data available but I must include all of the dates, so a date with no data available has a 0 in column B. I need to come up with a way to average the total of my data with the total number of weeks with data (ones without a 0) that will recalculate everytime I add new data. How do I write a function that will check column B for a number greater than 0 and then based on that result either count or ignore column A, and then total the number of times A is counted?

View Replies!   View Related
Count Numbers Between 2 Cell Values
I have columns of data that Im trying to count the number of times that one columns numbers fall between 2 other columns range. For the numbers in column F Im trying to get a count of the number of times that the numbers appear between the ranges in columns C to D. The total would increment in column E as these numbers were found in the range. Example: The numbers 60 (F2) and 150 (F3) would appear in the range 1 (C1) to 156 (D1). The count would be 2 which would appear in cell E1.

View Replies!   View Related
Count Unique Values Under The Influence Of Different Variables
My data: four variables A,B,C and D.

A growing dataset representing a group of people being nominated in different weeks for different jobs in different locations. Within a week and over several weeks an individual may be nominated for any number of jobs, in any number of locations.

A=week number (52 possibilities), B=job code (20 possibilities), C=location (2 possibilities), D=name (500+ possibilities) .....

View Replies!   View Related
Count/Sum Unique Values Based On Criteria
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 Replies!   View Related
Count Unique Values In A Pivot Table
I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.


View Replies!   View Related
Count Daily Minimum/Maximum Values
the macro will track daily min/max values, and keep a count of the times these values occurred in a separate sheet in the workbook.
At first I thought simply setting up a macro to look at a predefined range of rows to find the min/max values, but some days may be missing data; therefore, I need a macro with the logic that looks for the min/max value appropriate to each specific day (which is the tricky part). Below is the detailed explanation of the process.

1)The "Main" sheet, Column A, contains days 01/05/1999 01/29/1999 (days are already in numerical value format). Begin in A1 and this will be the first daily value. Find the Max Value in Column D corresponding to the numerical daily value of A1.
2)Once Max Value found in Column D, Column B of same row will have the time the Max Value occurred.
3)Count time Max Value occurred in Time Count sheet Column C of corresponding time.
4)Move down to next daily value. Continue this process to last row in sheet.

For example, Main Sheet, day 01/05/1999 (numerical daily value 36165), Max Value is 1.1835 found in Cell D12. B12 has the time at 2:45. A 1 is then placed next to 2:45 or C14 of the TimeCount sheet. If the next days Max Value occurred at the same time, then a 2 would be placed in C14...........................

View Replies!   View Related
Count Unique Values Based On Another Column
I am using Excel 2007, and I'm stuck with this problem.

View Replies!   View Related
COUNTIF To Count The Values In A Range Which Are Greater Than 0
How can I use COUNTIF to count the values in a range which are greater than 0?

I put =COUNTIF(B2:B100000)>0 but that didn't work?

(I'm using XL 2007 hence the range greater than 65000 rows. But I don't
think COUNTIFs syntax has been changed in 2007 so I assume I'm having a brain
fart.)

View Replies!   View Related
Count Unique Values In Pivot Table
I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed...

View Replies!   View Related
Count Unique Values In A Filtered Column
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.

View Replies!   View Related
Count Of Unique Values In Pivot Table
I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

View Replies!   View Related
Return Multiple Values To A Table And Count
I have a spreadsheet with two different rating scales (People & Business) that have a value of 1-5 per person. From this I created another column 'Sorter' that gives a person a single value of 1-25 (5*5 possibilities from the two rating scales.) I am trying to place people into a table based off of the column 'Sorter' as shown in columns U-Y. The real table cleaned up is in the table tab.

View Replies!   View Related
Count Unique Values With Specific Criteria
I need to count the amount of Unique codes in column A BUT ONLY if they are still OPEN status. Hopefully the attached example will be explaination enough (I want to automate the RED figure...

View Replies!   View Related
Count Of Multiple Values With Differing Weightings
Dave H kindly provided the formula below to sum all cells in a range with multiple values: =SUM(COUNTIF(D3:Y3,"a*"),COUNTIF(D3:Y3,"b"),COUNTIF(G2:Y2,"c"))

however my supervisor has just informed me that any A,B or C that appears in the J column is worth 4 instead of 1. Very annoying not to be given all the facts beforehand

so

J3...K3...L3
A....C....B

will equal 6 instead of 3

View Replies!   View Related
Count Unique Values Based On Matching Criteria
I have a worksheet with two tabs.

First Tab
Account Name
Account Number

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.

View Replies!   View Related
Count Cells Depending Upon Multiple Cell Values
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,

Order No OP Posted Set Allowed Set
30761157 00100.731.500
3076115700100.500.000
3076115700100.600.000
3076461000100.000.000
3076461000101.050.500
3076524400100.000.000
3076524400100.550.500
3076639600100.000.000
3076639600101.180.500
3076810900100.000.000
3076862900100.000.000
3076862900100.000.000
3076862900100.000.000

View Replies!   View Related
Count The Number Of Multiple Values In A Single Cell
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 Replies!   View Related
How To Count The Number Of Unique Values In A List, Based On Another Value
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 Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved