Count Entries With Two Previous Conditions
I am trying to count the number of entries in range BH3:BH621 when the cells in range B3:B621 = "Acting" and the range D3:D621 = "Feb"
I can do it with either the B range or the D range, but not both together.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Unique Entries In One Column That Meet Conditions
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 Replies!
View Related
Loop Deletes Previous Entries From Array
I think the loop is deleting my previous entries and only putting the last results in. For assortedrowindex = 3 To 400 targetdate = Date Do While Month(targetdate) = Month(Date) Redim Preserve arrTransactions(assortedrowindex - 2) arrTransactions(assortedrowindex - 2).CUSIP = Cells(assortedrowindex, 12) arrTransactions(assortedrowindex - 2).OrderDate = Cells(assortedrowindex, 9) arrTransactions(assortedrowindex - 2).BuyCurncy = Cells(assortedrowindex, 2) arrTransactions(assortedrowindex - 2).SelCuurncy = Cells(assortedrowindex, 4) arrTransactions(assortedrowindex - 2).Fund = Cells(assortedrowindex, 7) arrTransactions(assortedrowindex - 2).SettleDate = Cells(assortedrowindex, 10) arrTransactions(assortedrowindex - 2).BuyUnits = Cells(assortedrowindex, 15) arrTransactions(assortedrowindex - 2).FxRate = Cells(assortedrowindex, 16) If targetdate < arrTransactions(assortedrowindex - 2).SettleDate Then ' Sheets("Sheet2").Activate...............................
View Replies!
View Related
Count Formula: Count Total Entries In Columns
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View Replies!
View Related
Vlookup With Conditions To Find Multiple Entries
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008. A B C D 999 1/1/2008 1/15/2008 $10 999 1/15/2008 1/31/2008 $20 998 2/1/2008 - 2/25/2008 $15 I have another table (table2) in another sheet in the same workbook have a material and date. A B C 999 1/10/2008 999 1/20/2008 998 2/15/2008 My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2. I have tried using vlookup but it only works for the first match and doesn't check for other values below is the function that i tried =if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
View Replies!
View Related
Count Blank Cells Only If A 1 Is Previous
getting a formula to do this I have ......D E 5 )......1 6 )......2 7 )......3 8 )......4 9 )......5 10)......6 11)......7 etc down to 31 They only show up when the cell next to it is not empty. =IF(ISBLANK(D5),"0",(1)) =IF(ISBLANK(D6),"0",(2)) etc If nothing is put into say D5 D6 or D7 but something is put in D8 then i would like E8 to become 1 as it is the first to be filled.Then when D9 has something in it, it becomes 2 if D10 has nothing in it it gets left blank but when d11 has something in it e11 becomes 4 counting the blank cell in between. How can this be done.
View Replies!
View Related
Count Number Of Different Entries
I have a list of ID's, many of which appear several times. Is there a formula that will give me the number of different ID's? That is: CON001 CON100 CON050 CON001 the formula would give the answer "3" for 3 different numbers in the 4 total numbers.
View Replies!
View Related
Count By Unique Entries
I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'. I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column. An example, [Name].....................[Reason] C7 ...........................E7 John Potts.................Truancy 2 Matt Jones................Truancy 10 John Potts.................Truancy 4 Matt Jones ...............Late AM Pete Burns................Late PM Pete Burns ...............Late Both Steve Lopez..............Truancy 6 Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once] Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once]. I have also included a sample workbook.
View Replies!
View Related
Count Numbered Only Entries
I have a spreadsheet with data down column A. The data is either numeric or alpha numeric, however, it is not seen as numerical. Is there a formula I can use to count the total number of cells with only numbers in against other criteria too? I can use Sumproduct for 2 criteria but can't figure out how to do the 3rd.
View Replies!
View Related
Count Of Duplicate Entries
there are unique entries like AU0896 etc. that are repeated in my list. my job is to find how many unique entries there are and add the count at the end so, basically if there are 6 AU0896 entries, then I must create a AU08966 value.
View Replies!
View Related
Count Time (entries Per Hour)
I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time) For 12:00:00 all times would be between and including 12:00:00 and 12:59:59 Column B | Count ------------------ 12:00:00 344 13:00:00 44 14:00:00 5
View Replies!
View Related
Count Number Of Entries In A Sheet
I have a list of words in one column, some of which feature more than once, in random order, i.e.: Bird Plane Superman Superman Plane Superman Bird Plane I want to have a function that counts the number of times each word appears, so in the cell next to each entry for "Superman" it would say 3, for "Bird" 2 etc. If I add another "Superman" it should then change to 4 next to each entry. Also, I will be adding new words all the time, so the function needs to be able to cope with that too.
View Replies!
View Related
Count By 2 Conditions
i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2. Public Sub offloadDoor() Dim unassigned As Long unassigned = 0 For rowvar = 18 To 504 If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then unassigned = unassigned + 1 End If End If Next Sheet8.Cells("b3").Value = unassigned End Sub
View Replies!
View Related
Count Entries Only If Theres An Entry In The Cell To The Left
i'm trying to get a column to count all blanks but only if there's and entry in the cell to the left. for example i have a list of names which is picked up from my main database in column a, then in column b there's dates, non applicables and blanks. however the columns are longer than the list of names to allow for growth, so there's a lot of blanks at the bottom which i don't want to count. so is there a way to count only the blanks in column b if there's a name in column a alongside it
View Replies!
View Related
Merge Duplicate Entries For Count Or Sum
I often need to merge multiple occurences of data (such as account numbers or names) and to sum or count the values associated with each invividual instance (eg cost or number of entries). Data can often be thousands of rows and varies every time. For example: Col A Col B Ken 5.9 Ken 12.6 Brian 5.5 John 6.4 Fred 9.9 Fred 11.6 Fred 2.0 I need to be left with either a sum Ken 18.5 Brian 5.5..............
View Replies!
View Related
Count Conditions On Tabs
There are a variety of tabs on this database. These tabs track the large customers and specific brands. In the “Sales Rep Calendar” tab, I have attempted to calculate how many quotes are established 1) per Month and 2) per OS Sales Rep for 2007. Using formulas such as: ...
View Replies!
View Related
How To Use Count With Multiple Conditions
I have a table in Excel: The first row is time in years. The second row is method name,say,"A","B","C". I want to count the number when the time is less than 5 years AND "A" method is adopted. I tried this: count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work. how to revise the formula? In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as countif(C2:Z2,"<5")
View Replies!
View Related
Count For Instances With 2 Conditions
40,000 rows, Column A is a Port Code . . . always 4 digits Column B is a 2 digit code representing a mode of of transportation. I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .
View Replies!
View Related
Sumproduct To Count From 2 Conditions...
I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it. I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file). =SUMPRODUCT((C3:C124=1)*(D3:D124=0))
View Replies!
View Related
Count Unique Entries Within Variable Date Range
Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why. Goal: create a count of unique entries within a defined variable date range I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells. I've been attempting to use the DCOUNT function but I'm not getting the correct result. Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect. It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
View Replies!
View Related
Count The Number Of Entries On A Sheet That Match An Hour
I'm trying to Count the number of Entries on a Sheet that match an Hour. Looking through the availiable functions i found COUNTIFS, which is exactly what I want. However, when I try to compare the Hour values within the COUNTIFS arguments, there is an error. This is the function that I figured would work here: =COUNTIFS(HOUR(Sheet1!G:G), HOUR(E6)) which should count all entries in column G where its HOUR matches the HOUR in E6 (all are time format). I do realize that in the example above there is only one comparison made and i'm using COUNTIFS instead of COUNTIF, but i'll be adding other comparisons to it once i get this first comparison working.
View Replies!
View Related
Count Unique Entries Within Variable Date Range ..
I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View Replies!
View Related
Count Number Of Rows With Unique Entries In 2 Columns
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C. I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row). I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column. All the cust numbers are unique so would I be able to use a wildcard?
View Replies!
View Related
SUMPRODUCT - Count Multiple Conditions
Ive started using the sumproduct function to count multiple conditions which is useful howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that? the only way i can think is like the below =sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow)))) Rather than having to eliminate red and yellow i would like to say is green or blue.
View Replies!
View Related
Count Cell Value If Conditions Met
I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.
View Replies!
View Related
Count Cells Using Multiple Conditions
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 Replies!
View Related
Match And Count Unequal Ranges With Conditions
I have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated. For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave). The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells). For example: .....
View Replies!
View Related
Count And Sum Cells Meeting Two Conditions
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year. I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color. Next, I fill in each day with an employee name. Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this: Jones: Friday 4 (total number of days jones is in a blue box) Saturday 5 (...on a green box...and so on...) Sunday 3 Holiday 2 Monday-Thursday 50 For each employee name. Sounds easy, right? I can't get it to work!
View Replies!
View Related
Count Rows Matching Multiple Conditions
I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions. When "dates" or J2:J25 is less than or equal to today's date AND "HTeam" or W2:W25 is equal to Civil AND "Percent" or K2:K25 is equal to 100
View Replies!
View Related
Count Date Cells Where Date Is Previous Month
I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month. My first column shows who the request is from The second shows date recieved The third shows date we send receipt The fourth shows the date actioned.
View Replies!
View Related
Count With Conditions & Doesn't Exist In List
I have a Sumproduct formula to count instances of a particular event (from a list of events) based on multiple criteria. I am trying to utilize the same method to count instances of all events not defined in the list of events but I would welcome any solution In the attachment, Defined list of events A4;A5 (this is just an example, the actual list is approx 100 events) Data being counted F2:N10 (actual data approx 1000 rows) My working formula is in cells B4 through D5 My not working attempt to adapt the formula B6
View Replies!
View Related
Make One Cell Count Twice In AVG If Certain Text Conditions Apply- 2008
I am using Excel to tabulate votes for a contest. Judges have given a number to each entry, and but certain judges' opinions need to count twice as much as other judges' opinions based on their qualifications. I've attached the file to help illustrate what I'm trying to do. Morris's votes need to count twice for all Photography or Web Design entries, and Clark's votes need to count twice for all Graphic Design or Web Design entries. I know I can do this manually by simply copying the number into a blank cell in another column (like the blank column between Morris and Clark's names), but is there any way to make Excel do this for me?
View Replies!
View Related
Count Number Of Cells That Meet Specific Conditions - Error Messages
I have a spreadsheet which is linked to several other worksheets. I have managed to include formulas to count how many cells have numbers between 101 and 5000 by using this formula - =sum((h2:h500>=101)*(h2:h500<=5000)) but now I want to count the number of cells in another worksheet that are equal to or less than zero. When I use the same formula as above it counts all the blank cells. I have tried using a countblank formula and then deducting this from the result, but unless the other worksheet is open the countblank formula does not work.
View Replies!
View Related
Count Unique Logs With Multiple Conditions Of Multiple Sheets
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 Replies!
View Related
|
TRACKER
