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
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
I have a sheet named "XYZ Activity" with meeting dates in column B starting with cell B4. There are duplicates in this list, that should only be viewed as one meeting. On another sheet, each company that attends the meeting has a specific join date found in Column C (C4 is the first company start date). I am trying to figure out an equation that will count how many meetings the company could have attended. I already calculate the total meetings they have attended using either of the following equations:
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've come across a formula that I can't seem to get my mind fully around. I have a time-tracking sheet for employees that includes, among other fields, DATE and MILEAGE.
In my particular spreadsheet, DATE is from A2:A31 and MILEAGE is from G2:G31. The date column is set to the correct data type (date) and mileage is a general field and only has integers entered into it.
What I would like to do is count the number of unique dates on which mileage has been entered (field is not empty). If the total mileage for a day is less than 20, then do not count it. The reason for this is because an employee is entitled to be compensated for the total mileage of all service calls of a particular day minus 20 kilometers. If an employee, for whatever reason, enters say, 5km for the day, they should not be deducted 15km as would happen if the date was counted.
For example, if Jack traveled a total of 500km spread over 5 days, he is entitled to be compensated for 500 - (20 * 5) = 400km. The trick here is that if Joe has time billed on 6 unique days but only entered mileage on 5 of them, the formula still needs to return 5.
This formula sums up the total mileage traveled and then subtracts 20 * number of unique days. The problems with it is that it still counts days on which no mileage was claimed (corresponding mileage field is empty). In addition, if the total accumulated mileage for a day is less than 20 then it is also still counted.
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'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'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 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?
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 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.
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).
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): ...
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.
What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.
A Criteria B A Criteria C Criteria A C Criteria C D Criteria 1 2 Criteria 3
Criteria 2 3 Criteria
I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.
I need to count the amount of unique values in a range, but only count unique values that also have a seperate condition. To illustrate with the 2 column dataset below....
Colour Letter Yellow A Yellow A Green B Yellow B Blue A Blue A Yellow C
For this set of data i want 3 seperate cells for each 'Letter' telling me that, for 'A', the answer is 2 unique values (i.e. Yellow and Blue). For 'B' there is also 2 unique values (i.e. Green and Yellow) and finally for 'C' there is just 1 unique value (i.e. yellow).
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.