I am trying to count unique values of the column tienda with the unique values of the column promo. Output expected below table.
promotienda
pablo a
pablo a
pablo b
juan c
juan c
juan c
jesus d
jesus r
Output:
Pablo: 2
Juan: 1
Jesus: 2
2013
Current Year
Q1-13
Q2-13
Q3-13
Q4-13
Totals
Awarded
£19,000.00
£4,000.00
£3,250.00
£0.00
£26,250.00
[code].....
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
I have the following data in 1 of the tabs.
[TABLE]
ClassSequenceAB
10SE132422
20SE23212
20SE321
20SE23425
10SE332455
15SE132412
10SE234
[/TABLE]
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE]
ClassAB
1032
1511
2012
[/TABLE]
Can this be achieved through a formula?
I have some data in a column, starting at row 6 that I'd like to find the number of unique text values for. For this I have been using this formula
Code:
{=SUM(IF(FREQUENCY(IF(LEN(A6:A10000)>0,MATCH(A6:A10000,A6:A10000,0),""), IF(LEN(A6:A10000)>0,MATCH(A6:A10000,A6:A10000,0),""))>0,1))}
The data is dynamic so I picked an arbitrarily large number (10000) and the above formula successfully ignores blank fields.
However, I'd like to now find the count of unique text values that end in X. For example, let's say the data are as follows
Dog_Cat
Mouse_Dog
Mouse_Cat
Mouse_Cat
Mouse_Cat
Mouse_Mouse
Elephant_Elephant
How would I go about (in one function) finding the unique values that end in "_Cat", in this case 2 ("Mouse_Cat" and "Dog_Cat")?
I have a worksheet with following data in 1 tab.
[TABLE]
Seq Class
SE110
SE270
SE110
SE370
SE310
SE110
SE170
SE370
[/TABLE]
In a different tab I want to calculate the unique count of sequence for a specified class.
In above eg: for class 10 it should return 2
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
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.
I'm trying find a way when running a report that continues to grow weekly - to either run with a script or a specific formula. There's certain cells that I'm trying to capture but the main thing is it's very time consuming and is tracked onto a separate workbook. Here's what I'm looking for a hand on:
Weekly list of 20,000+ rows - includes customer information - names, addresses, phone, account #, order #, etc I sort my list by address and then look and manually count the duplicates. Once duplicates are located I enter on a separate workbook along with the most recent order number. There has to be a faster way.
I am analyzing a quota schedule. I need to count the cells in a row until I hit a value in one of the cells in that row (lets say cell>=30). Then I want the formula to evaluate the next two cells in front of that cell to determine if either is >=30, if so then the formula counts up to the original cell. This will determine # of days out for a install or trouble call lets say.
I have messed with a combination of IF, CountIF, and Match statements and just seem to hit a dead end. Any thoughts, even if it is that I may need another approach, is welcome.
Here is some code I have tried to no avail: ....
How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?
For e.g., in Column A (row 1 - 10):
Proj-001
Proj-001
Proj-002
Proj-004
#N/A
#N/A
Proj-007
Proj-002
Basically i need to work out if a3 matches one of the dates in column 1:1 i need it to calculate and give me the sum for the weekly total of b3, d3, f3, h3, j3, l3 and so on if column a4 matches one of the dates in column 1:1
Mon/02/09/13
Tue/03/09/13
Wed/04/09/13
Thu/05/09/13
Fri/06/09/13
Weekly Total
[Code] .......
I would like to check if there's a way to count the number of unique value with a given condition
Eg. condition where parcel count <> 0, also want to find out the unique value of district and postal sector
Attached is the files, and my desired result is highlighted in RED.
From the highlighted cells at B17 to B19, while using some kind of lookup or indexing method, I want to count the number of cells in that specific range that's great than zero. This list is just a sample, the list will be much bigger and order may not be the same--that's why I need to lookup the project name.
View 7 Replies View RelatedI 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
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 RelatedIn the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG
At the moment I have a formula as below:
=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.
Book1.xlsx
Have numerous values in Col A. Col E extracts a list of unique values from that column.
In Col C, the Col A value has had characters added to it.
Need a formula to count the number of unique values from Col C which contain the same prefix from Col A, and place the result in Col F.
A sample workdook is attached with the desired result shown and highlighted in yellow.
Look up a cell value from another workbook and count the match
View 9 Replies View RelatedHere is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
I have 2 columns that I need to use simultaneously to conclude something.
Column A
Row 1: Dog
Row 2: Cat
Row 3: Bird
Row 4: Dog
Row 5: Dog
Row 6: Dog
Column B
Row 1: See Vet
Row 2: See Vet
Row 3: Get Rest
Row 4: Get Rest
Row 5: Get Rest
Row 6: See Vet
Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
I'm a newbie to these forums, but I had a question that I couldn't find an answer to in the search feature. So, I joined and here I go....
I have a whole lotta data (nearly 600 "pages" of data) that I pasted into Excel. Unfortunately, it's formatted poorly and I'd like to make it nice and purty.
What I started to do was to create a 2nd sheet and then do an "=" and then click the spot with the 'group name' and then another "=" underneath it and then clicked the spot with the 'premium' info, etc.
After a couple of these, I figured that I could simply make a formula to help me out with it. Please see the attached screen captures for some detail on my issue.
Can someone help me create a formula or a way to count a certain cell and then 52 cells underneath it and display it?
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies View RelatedI am trying to give a cell a unique ID based on the contents of 2 other cells.
Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
I would like to know how to combine multiple cell value from each column to create a unique value. for example column A will have values (a,a,b,c,d,e,e,f,i,j) and B will have (1,2,1,1,3,4,5,5,6,7) and C has ( xxx,yyy,zzz,xxx,yyy,zzz,xxx,xxx,yyy,zzz).
i need to create a list of unique possible combination of data into column D.
I have placed below example and how to create a similar code and what functionality does this.
A
A Code
B
B Code
C
C Code
D
D Code
[code]...
HOw can I match combination columns of A-F (row1) to combination H-M (rows1 to end) and show how
many are number matched in column N..
ex..
Rows
A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
2
3
16
17
11
9
11
17
18
In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.
View 9 Replies View RelatedI am looking for a formula that matches two unique numbers in a table based on two numbers that are entered in two other cells. This will be clearer by looking at the example below and the attached.
The goal is to find the number entered for the home field (cell c2) in the "home" row (B5:K5) with the number entered for the away field (cell c3) in the "away" column (A6:A15) and result in the data element where the row and columns come together. The expected result for this example in the attached would be "JPD".
Another example would be if a 6 was in cell c2 and an 8 was in c3, the expected result would be "RBK". I think this can be done by indexing and matching but my memory has gone blank with how this works.
i have over 1000 line invoice # with multiple listings....however they have different dollar values for each item
702-0195660 = $10
702-0195660 = $12
702-0195660 = $15
i have an index match in my "missing worksheet" **see attached**
but that is looking up only the first value
702-0195660 = $10
702-0195660 = $10 (should be $12)
702-0195660 = $10 (should be $15)
etc
i have attached the sheet..ive been on the past day!
attached file link below [URL]...
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:
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.
