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?

I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.

Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.

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

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

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

I would like to achieve the following without using vba if possible, though I realise it may not be possible.

Using a formula, I want to count the number of rows which contain one or more of a number of options.

[URL] ....

In the picture there are 6 or so clients: The columns to the right indicate with the letters A, B and C whether these individuals have traits 'A' 'B' or 'C'. So effectively I just want to count the number of clients(rows) which have one or more trait.

Question: Can I do this using just a formula, and if so, how?Workbook attached.

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?

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.

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.

I am trying to do up a summary for an attendance sheet that I have.

The problem is I have a number of values that could all qualify as a valid attendance marking and others I need to ignore

I have that list of value in a named range "Present" - Column G on the Lookups Sheet

And I have three sheets I need to add together

The layout of the three attendance sheets is directed so I can't mess with that - and we need uniformity across all the organisations that record attendances.

On the first sheet/tab I have entered some example markings - the "C" markings need to be ignored as they aren't in my list of valid values

The Summary sheet is still counting them as it is currently counting anything <>"".

I guess in short ... how doe I change <>"" to my named range "Present"

I have zipped the workbook as it is 445kb (as a result of some lots of conditional formatting and terrible layout)

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

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.

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.

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.

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?

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

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?

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

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.

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

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.

I'm sure there's a simple way to do this (I've been trying countif/match formulas but I'm not quite there)! I need to group line items with the same dollar value, WITHIN say 1 cent. If I do a pivot it will group exact matches, but I'm looking for the flexibility to group something within a few cents. So instead of pivots, I was trying to think of a formula that would do what Column A is doing below:

*I manually added Column A, looking for a formula that would group dollar values within 1 cent, assuming I've sorted Column B from smallest to largest:

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.

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.

Currently I have 3 Columns. The first column is a list of phone numbers that have confirmed and bought our product. The second column holds the phone numbers of the initial leads that we generated. And the third column is the keyword that the lead used to find our website.

What I need to do is match the phone number from Column A with the phone number of Column B then record the frequency of the keyword that was used to produce that lead.

I've attached a screen shot with the first few rows of my table (Nearly 1000 in total) as well as an example of what I imagine the result looking like.

First Few Rows Of The Table: Excel-Keyword-Matching.jpg

What I Am Looking To Do: Keyword-Frequency-Complete-Example.jpg

I have 300 cells filled with around a dozen different names - i.e. the same names crop up multiple times. What I need is automatically creating a list of all the names that appear, ordered by how many times they appear and with the number of times they appear next to them - i.e. ranking the names by their frequency.

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

formula to find duplicates and count only on instance of them.

image below:

Uploaded with ImageShack.us

As you could see on column A i have several entries,some of them duplicated. In column B i have a formula to check weather the value on column A is duplicated or Not. From this values i want to count only one instance of duplicated entries. I want to have one instance of Oscar to be counted as original and regardless the number of duplicates i want to count only 1. i want this not only for Oscar but for all the duplicates with more than 1 duplicate result.