Count Unique Occurrences Based On Multiple Conditions In Another Cells
Sep 19, 2013
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.
View 8 Replies
ADVERTISEMENT
Feb 22, 2011
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
View 4 Replies
View Related
Mar 18, 2013
I've got two columns, one with policy numbers, the other with error codes.
A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function
=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))
I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with
=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))
The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.
What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?
Here's my sample data: [URL]
View 9 Replies
View Related
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 Replies
View Related
Apr 28, 2014
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
=SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))
View 4 Replies
View Related
Jan 2, 2009
I am looking for a solution other than using an advanced data filter for unique records only.
I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).
Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.
Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?
SEE ATTACHED:
View 9 Replies
View Related
Apr 12, 2014
I'm trying to count the number of occurrences where two conditions in a table are true.
I have a table that has two columns for ratings; impact and probability. Each can be scored 1-5 This creates a matrix table of possible scores from 1 - 25 (image attached)
I want to COUNT the number of items in each of the boxes (not the total score). For example, how many are Impact 5 and Probability 5 (25 total); how many are Impact 4 and Probability 2 (8 total), and so on. Basically a count of the each of the intersections in the matrix.
Something like "Countif Impact is 5 AND Probability is 5"
Is it possible to count something once, checking for multiple conditions?
View 2 Replies
View Related
May 25, 2014
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A
ABCD
ABCD
ABCE
BDCE
BDCF
ZXYE
ZXYE
YEZY
CDEF
Col B is where i would like to list all the unquie vaules with a count e.g.
ABCD 2
ABCE 1
BDCE 1
BDCF 1
ZXYE 2
YEZY 1
CDEF 1
View 14 Replies
View Related
Nov 2, 2011
I'm trying to write a formula that will count the number of unique occurrences in a column, if a specified value is found in a different column.
So I want to count the number of unique values in the "ID" column if let's say the text "NameA" appears in the "Name" column.
ID Name 12345
NameA
NameB
NameA 12346
View 5 Replies
View Related
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
Jul 1, 2008
I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.
I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes
View 4 Replies
View Related
Jan 28, 2013
I have an issue log containing (separate) columns with dates, numbers and text.
One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)
I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).
After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.
So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".
After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.
View 3 Replies
View Related
Jan 6, 2008
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?
View 8 Replies
View Related
Jan 3, 2008
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
View 3 Replies
View Related
Jul 16, 2013
I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:
Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i
but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.
View 4 Replies
View Related
Jan 17, 2010
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]
The situation:
I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.
namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm
The challenge:
In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!
The correct result: ...
View 6 Replies
View Related
Feb 25, 2014
Iam doing a market report for real estate. I have attached a file for reference. I am trying to track the Active Listings within a given time period and within a specific zip code. Unfortunately my local MLS does not track historical data on Active Listings, and therefore I have to use the Listing Date and the date it no longer went active to determine which listings were active in a given time period. Some of the listings are still active and therefore do not have a "No Longer Active" date. These listings will need to be counted as well.
MLS Active by Zip.xlsx
View 6 Replies
View Related
Jan 15, 2008
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
View 6 Replies
View Related
Jul 25, 2014
how to count the unique occurrences in column A based on the value in column B. In the example below, I am trying to count the number of unique names in each state (Illinois=1, Colorado=2, New York=2). I would like the formula to count unique occurrences for the entire column A because I will be adding to the list.
..A.............B............C
John......Illinois
John.....Illinois
John.....Colorado
Alex.....Colorado
Alex.....New York
Steve...New York
Steve...New York
View 10 Replies
View Related
Jun 12, 2009
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
View 6 Replies
View Related
Jun 18, 2014
I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.
Code to fill these blank cells automatically with some specific texts based on the attached conditions.
I have attached the workbook for more details. FillCellsTest.xlsm‎
View 10 Replies
View Related
Oct 9, 2009
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
View 10 Replies
View Related
Jan 30, 2014
I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.
Type
Description
Concern
Formal
Informal
[Code] ....
View 6 Replies
View Related
Mar 14, 2014
I have my workbook set up so that one worksheet contains the names of all my departments/sections listed with their 3 letter code (this can be anything from AAA-ZZZ, e.g 'FYH', 'JWK' or 'KZA' are all valid) in Column F. I then have Columns H, I, J & K blank and require them to be auto-filling based on data from 4 other worksheets - i'll detail what i need below, but Column H needs to be filled based on what it finds in worksheet 'Data 1', Column I should be filled based on what it finds in worksheet 'Data 2' etc. etc. I have four further worksheets that contain data based on the types of assets owned by the department/section, these are 'Data 1', 'Data 2', 'Data 3' and 'Data 4' and I use these as described above, with Column H referring only to 'Data 1', Column I only to 'Data 2', Column J only to 'Data 3' and Column K only to 'Data 4'.
What i need then, is a VBA that will take the value of a cell in column F on my main worksheet (called 'main') and then count the number of occurences of that value in Coulumn A on another worksheet and place the number of occurences into Column H, I, J or K based on which sheet it's checking.
For example, Cell 'F2' on my main worksheet contains the value "ABC". I need to take "ABC" and search Column A of worksheet "Data 1" to find how many time it occurs, placing the result into Column H of worksheet 'Main', I then need the same value to be counted in Column A of 'Data 2' and its result placed into Column I on 'Main', and again for Column A of 'Data 3' with the result being put into Column J of 'Main', and finally Column A of 'Data 4' to be checked and that result placed into Column K on 'Main'.
does this seem too much of a task for VBA? or am i best doing each column separately with a CountIf or Array formula?? Not sure whether a COUNTIF or Array formula will do the job.
View 5 Replies
View Related
Feb 5, 2013
I get this work a lot and am looking for a much more effective way of working the dataset,
I have numbers like 1.1, 1.2, 1.3 and so on.......
These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....
I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.
I am attaching a sample document to see how it is laid out. [URL] .....
View 6 Replies
View Related
Mar 14, 2014
I have my workbook set up so that one worksheet contains the names of all my departments/sections listed with their 3 letter code (this can be anything from AAA-ZZZ, e.g 'FYH', 'JWK' or 'KZA' are all valid) in Column F. I then have Columns H, I, J & K blank and require them to be auto-filling based on data from 4 other worksheets - i'll detail what i need below, but Column H needs to be filled based on what it finds in worksheet 'Data 1', Column I should be filled based on what it finds in worksheet 'Data 2' etc. etc. I have four further worksheets that contain data based on the types of assets owned by the department/section, these are 'Data 1', 'Data 2', 'Data 3' and 'Data 4' and I use these as described above, with Column H referring only to 'Data 1', Column I only to 'Data 2', Column J only to 'Data 3' and Column K only to 'Data 4'.
What i need then, is a VBA that will take the value of a cell in column F on my main worksheet (called 'main') and then count the number of occurences of that value in Coulumn A on another worksheet and place the number of occurences into Column H, I, J or K based on which sheet it's checking.
For example, Cell 'F2' on my main worksheet contains the value "ABC". I need to take "ABC" and search Column A of worksheet "Data 1" to find how many time it occurs, placing the result into Column H of worksheet 'Main', I then need the same value to be counted in Column A of 'Data 2' and its result placed into Column I on 'Main', and again for Column A of 'Data 3' with the result being put into Column J of 'Main', and finally Column A of 'Data 4' to be checked and that result placed into Column K on 'Main'.
does this seem too much of a task for VBA? or am i best doing each column separately with a CountIf or Array formula?
View 2 Replies
View Related
May 9, 2008
I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.
What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.
A simple table example would be:
colA colB colC
Dan Complex Build House
Dan Complex Build Shopping center
Dan Simple Take dog to walk
Jose Simple Clean desk
Maria Medium Paint office roof
Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex"))
But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...
View 3 Replies
View Related
Jun 11, 2014
In Column A I have the list of strings that I need to search. I want to count the number of occurrences of these strings inside these two text files: Sample1.txt and Sample2.txt. For example I have the string "DOG". I want to search and count the number of times this string appeared in Sample1.txt, and Sample2.txt
See below:
View 5 Replies
View Related
May 8, 2008
I am attaching a sheet in which Data can be entered in cells C5 & D5 in "Calculation " sheet, depending upon the value of C5 it will count all the Occurrence of data of D5 in adjacent cell i.e. (one up & and one below of data of C5) . data will search in "PCM_PLAN" sheet in column " I ".
count will be given in E5 in "calculation sheet"
View 9 Replies
View Related
May 7, 2014
I need counting unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.
The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D<=Z4,G:G)))),G:G),1))
The goal is to count all of the unique value in column G:G that meet certain criteria (have ending times greater than Z3 and beginning times less than Z4, etc.). Once I get the formula to work, I'll simply drag it down to have it calculate for similar lightly changing criteria (like different beginning and ending intervals). However, the formula doesn't seem to be working. It's only giving me zero values (I have "ctrl-shift-entered").
View 9 Replies
View Related
