Count W/multiple Criteria, Based On Date
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Cells Based On Multiple OR Criteria
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR. Is there a nested COUNTIFS or similar function that would work?
View Replies!
View Related
SUM (count) - Multiple Criteria Including Date Ranges
My setup is - excel 2003 sp3 / windows xp On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths. Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received. =SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
View Replies!
View Related
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
Multiple Criteria Count Between 2 Dates & Other Criteria
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley. I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried - =SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50)) But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
View Replies!
View Related
Count Rows Meeting Multiple Criteria Of Multiple Values
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not. I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K. I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
View Replies!
View Related
Count With Multiple Criteria
I need to somehow count the number of times two criteria are met in a certain set of data and store it in a cell on a seperate sheet. My data is all stored in a sheet called Data and the conditions are as follows: 1. Column C = WEB AND 2. Column D = Cancelled So I need to count the number of times both these events occur and save them in my other sheet called Metrics. i lokoed it up on google but all the formulas I found referenced the same sheet and wouldn't work for my data set.
View Replies!
View Related
Count If Based On Two Criteria
I am trying to count AE2:AE601 if AE2:AE601 <= 2 and if AF2:AF601 = the value of AH2 but the formula that I have create returns a 0 when it should be returning a 1. Where did I go wrong? Here is what I have so far. =COUNT((AE2:AE601)*(IF(AE2:AE601,"<=2",IF(AF2:AF601,"="&AH2))))
View Replies!
View Related
Count Based On Criteria
I have a spreadsheet for my customers. i Break them down in 3 categories L c r Column o gives me the total dollar amount of all columns. I have figured out how to tell how many of each I have sepratly and there respective dollar totals How do count how many $0 amounts in column o belong to l r c respectively In other words if I had 10 c's how many were $0 in o. Lets say I had 10 c's but only 6 had a total greater then $0 in column o that would be 60 percent
View Replies!
View Related
Sumproduct - Count More Than One Criteria By Date
I have a document with the following data 1: NAME 2: DESCRIPTION 3: DATE "12/15/2007" format I would like to know if anyone knows how I can perform the following. And please excuse me for not writing this very technically I am a bit lost. if name = "JOHN" and description = "APPLE" count how many apples there are for john. Now it gets complicated for me because the dates are as follows. Lets say it starts 1/1/2004 the next cell might be 1/3/2204, 1/6/2204, etc all the way to 2008. I would like to find a way to say If date = day/month/year I would like to count the whole month instead of the individual days. am I making sence? Let me give you another example. record reads "," represent cells JOHN,1/2/2204,APPLE JOHN,1/3/2204,ORANGE JOHN,2/3/2004,APPLE I would like to be able to come up with the following JOHN had 1 APPLE in JAN 2004 JOHN had 1 ORANGE in JAN 2004
View Replies!
View Related
Unique Count With Multiple Criteria
For example, each job has a specific ref number and within that job ref number there is a number of lines of data, some of which we ignore and some of which we include. Each job will have a planning time using an old methodology (Planning A), a planning time using a new methodology (Planning B) and an actual time. There are a few other variables too such as the category of work. I am trying to count the number of each unique ref no. within the sub category of work I am looking at (there are circa 50 sub categories of work). Each ref no. will only have 1 combination of work cat and sub category. For example: ....
View Replies!
View Related
Count According To Multiple Criteria. Dcount?
OK, I have a list of sales that my semester long company has sold. In the midst of these sales we are sellling a versoin of the bumble watches. people can either buy a watch, (which includes a face and a band) a face, or a band. So I have this giant list and only like 10 watches on there. I need a formula that will count how many watches and faces we sell. I was initially thinking something along the lines of: =COUNT(OR('Sales Journal'!D:D="Watch",'Sales Journal'!D:D="Face")) =COUNT(OR('Sales Journal'!D:D="Watch",'Sales Journal'!D:D="Band")) Which worked great until I had more than one watch in there. I can't figure out dcount, and don't know if it's even the right thing to use in this instance. If you want I can post the sales journal..
View Replies!
View Related
Running Multiple Criteria Count
Have list of employees and their dependents organized into the following columns: Employee ID (unique to employee), Relationship Code (E=employee, S=Spouse, D=child), andBirthDate. Would like to add a column called Sequence Code which counts the family members (employee= 0, S=1, oldest D=2, next oldest D = 3, and so on). Employee ID RelationshipCode BirthDate Sequence Code 100006641 E 2/5/75 0 100006641 S 1/2/75 1 100006641 D 3/9/89 2 100005555 E 2/5/77 0 100005555 D 1/2/89 2 100005555 D 3/9/88 1 Note that the list is sorted by Employee ID, but not Birthdate. Can this be done with a formula in the Sequence Code column? I really suck at VBA
View Replies!
View Related
COUNT Or FREQUENCY With Multiple Criteria
I have a list of 267 individuals who are each in one of 4 class designations (A,B,C, & D). They produce widgets for me. The individuals’ names are in A2:A268, their class designation is in B2:B268; and the number of widgets they each produced is in C2:C268. Row 1 is a header row. I used the FREQUENCY function, for the first time, to show how many of the individuals produced 1-10, 11-20, 21-30, 31-40, 41-50, 51-60, and 60+ widgets last year. NOW, I’d like to take it a step further and show the number of individuals that produced widgets in each of these number groupings, but want it further split out by their A, B, C, D, class designation. So, I want to see how many individuals in the “A” class that produced 1-10, 11-20, 21-30, etc. widgets; and how many individuals in the “B” class produced those numbers, etc. I’ve been playing around with using the COUNT and IF functions together and the FREQUENCY and IF functions, but have not been successful. Am I making this more complicated than it is? By the way, their class designation doesn’t have any relation to the number of widgets they produced, and the data is sorted alphabetically by the individuals’ names.
View Replies!
View Related
Count Matching Multiple Criteria
I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got
View Replies!
View Related
Count 3 Criteria Based On 3 Ranges
I am attempting to count records based on 3 criteria in 3 different ranges and am hitting a wall. My formula will be on a different worksheet as it is the summary sheet. Worksheet "Cases" Column E - Type Column F - Month Column G - Year Worksheet "Recap" Recap month will be input into Cell I5 eg. "6" for June Recap year will be input into Cell K5 eg. "2009" Cells N10-N15 is a list of Types Summary Cell J10 will need to count if 1)worksheet Cases column E = worksheet Recap N10 2)worksheet Cases column F = worksheet Recap I5 3)worksheet Cases column G = worksheet Recap K5
View Replies!
View Related
Count Data Based On 2 Criteria
I've looked at the various examples in a search of topics simillar to my topic but I still can not get thsi to work. I have a work book that contains 2 sheets Sheet1 is called RawData of Multiple columns Sheet2 is a quick report basded on Sheet1 data. I need to find a way to do counts where 2 criteria are met. Example. Count the number of Times the phrase Major Victory appears in column M ( Range M1 to M50 ) of Sheet1 IF the value in Column L ( Range L1 to L50 ) of Sheet1 = USA I tried this: =COUNTIF(RawData!M1:M50,B1( AND(RawData!L1:L50=$A1))) I am using B1 and A1 ( from Sheet2 ) as search criteria but it gave me 0 ( and I know this is wrong ). Based on the reading I did on simillar topics it appears that I cannot use CountIF in this case.
View Replies!
View Related
Macro To Count Based On Criteria
I have ahseet with the following columns: Col 1 = Item Description Col2 = Price Col3 = Discount Rate I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
View Replies!
View Related
Sum/Count Based On Many Criteria
This is probably a simple question for excel gurus, but I am a molecular biologist, and don't set up these formulas every day. I have a table full of genotypes that I have indicated as 0, 1, 2 or 3 in columns. Column heads are gene names; row heads are subjects (patient numbers). I want excel to look in row 1 in column A and see if there is a 1 and also in column B in the same row to see if there is a 1, and if both are 1, count that patient in a tally. I want to be able to get a count of the number of patients with 1's in both columns. If someone can tell me what functions I use to set up this tally, I think I can set up more elaborate formulas. At the end, I want excel to look at several columns and tally the number of patients that have 1's in several columns.
View Replies!
View Related
Count With Multiple Criteria And Find Permutations
I have a few calculations I cannot solve for life of me. I know you guys can figure this out. In the example attached you will see the list of the numbers that were drawn (More numbers will be added as time continues). I was able to use " countif" to find the total number of time each number was drawn. My delima, I need: 1. Count the number of times each number was drawn by person. John starting in cell J7 Mary starting in cell J11 2. I need to type in 4 digits in cell J19, and find all possible combinations from the list of numbers drawn List what combination of numbers that was drawn including the date it was drawn by person starting at cell I20 using the format as shown
View Replies!
View Related
Count And Sum For Criteria In Multiple Columns
I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data. I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount. 1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B. 2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B. Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.
View Replies!
View Related
Count Cells Not Meeting Multiple Criteria
I have what I thought would be a simple task, but after almost a day of searching this and other sites, I've gotten no where. I have two issues that are similar. First, I need to edit my VBA code to count the number of blank cells in a column. Here's the code I'm starting with...
View Replies!
View Related
Count Cells Matching Multiple Criteria
I am using the below code to count records in a range that meet multiple criteria. myCount=0 For Each dCell In Range("MyRange") If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1 Next dCell Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).
View Replies!
View Related
Count By Criteria Based On Duplicates In Different Range
I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example. Row A Row B 25 John Smith 50 Mike Jones 25 Chris Carter 50 Mike Jones I want to be able to count one number in "A" but only if the name is not duplicated in "B". My result for #25 would be 2. My result for #50 would be 1, because Mike Jones is duplicated. The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.
View Replies!
View Related
Count Of Unique Numbers Based On A Criteria
My data is like: A1: 5 -- B1: X A2: 5 -- B2: X A3: 5 -- B3: Y A4: 3 -- B4: Y A5: 3 -- B5: X A6: 8 -- B6: Y A7: 2 -- B7: X A8: 3 -- B8: Y A9: 1 -- B9: X I want the count of unique numbers in column A based on the criteria i have on column B. So the count for X should be 4 and for Y should be 3.. Can i achieve this using formula only?
View Replies!
View Related
Count Between Date Range & Text Criteria
I have an excel file with 1000 of records for eg. Date INITIALS CATEGORYACATEGORYB 31-Dec-07MTSVIN 01-Jan-08MSSVDI 04-Jan-08MORSKH 31-Jan-08MPSVHI 02-Feb-08MPRSIN 03-Feb-08MPVSIN 02-Jan-08MSRSIN 03-Mar-08MSKSKI i want to count the datas 1) SELECTING IN THE SPECIFIED DATE RANGE (e.g from 1st jan to 31st Jan) 2) SELECTING MULTIPLE CRITERIA IN B COLUMN (MP & MS) 3) SELECTING MULTIPLE CRITERIA IN C COLUMN (SV & RS) 4) SELECTING MULTIPLE CRITERIA IN D COLUMN (IN & HI) My colleagues tried countifs in excel 2007 but i have 2003. i want to specify all the four criteria in a single function to achieve my result (IN EACH MULTIPLE CRITERIA)
View Replies!
View Related
Count Multiple Values In Cell With Range Criteria
I'm trying to figure out how to count by two criteria, only one of the criteria has a value among multiple values in a cell and the other is a range. I tried to use sum but it doesn't count the value and count doesn't recognize the range. I attached the file so you can see what we're trying to do.
View Replies!
View Related
SUMPRODUCT With Multiple Criteria: Count The Number Of Documents
I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column. I have tried using this SUMPRODUCT formula: =SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones"))) but it is generating incorrect values! Specifically: - Jones shoulld return 1 - Franks should return 3 - Smith shoudl return 0
View Replies!
View Related
Count Unique Records With Multiple Criteria With Formula
Count unique records in Column B where. 1.) ... Column H >=A1 and <=A2 2.) ... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2 3.) ... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2 4.) ... Column H <>"" and Column I >=A1 and <=A2 and Column J ="" A1 - user defined (start) Date 1 A2 - user defined (end) Date 2 Column B - 6 digit number (or blank) Column H - Date 3 (or blank) Column I - Date 4 (or "expired" or blank) Column J - Date 5 (or blank)
View Replies!
View Related
Count/Sum Unique Values Based On Criteria
I'm facing a big problem and I can't found a solution for days. I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below: =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")) Let me Put to you an example for you been able to understand my problem. Example considering Range "C2:C20" exluding blank cells (1) - To count unique values =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")) (2) - To count unique values based on criteria in range "B2:B20" =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")) (3) - To sum unique values on "E2:E20" based on same criteria =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20)) They all work fine. But I need to insert a merge at Row 10 for better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's. I just don't know what to do guys ....
View Replies!
View Related
Count Unique Values Based On Matching Criteria
I have a worksheet with two tabs. First Tab Account Name Account Number Second Tab Account Name Account Number Account Ship Location Number On the first sheet each account name and number only appears once. On the second sheet there may be multiple entries for each Account Name and Number. On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
View Replies!
View Related
Macro: Count Data In Range Based On Criteria
I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this. For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.
View Replies!
View Related
Count Based On Date Range
I have one worksheet with 2 columns: A - Dates, B - User IDs. In another worksheet I would like to have two columns: A - User IDs and B - Count. The count in Worksheet2, column B would be a count of how many instances the user ID in Worksheet1 appears before or on a certain date. Example:..................
View Replies!
View Related
Using DCOUNT And Date Based Criteria
i'm trying to write a summary page for the data collect on the first sheet and would like to find a way of counting the number of records within certain date criteria (those that are more than 30 days old). now the crucial part is that the records have a second set of critical data, their Status, and i need to find only the records that have a particular status (in this example that is Pending and Open). if you look at the second sheet you'll see that i can find records that are associated with a specific date, but not with a range of dates; which is particularly annoying because DCOUNT can find ranges of information when using other types of number.
View Replies!
View Related
Add To Date Based On Criteria
I wanted to solve a problem in excel. For a particular period there will be flow of units on a daily basis, which can be more or less than actual capacity. Flow received will be done on first in first out basis. I wanted to know the completion date for the same in excel. I'm attaching the sample sheet of the same.
View Replies!
View Related
Subtotal Based On Multiple Criteria
I need a formula that will take this information and automatically insert a row, a total, and then another row every time the Product (Column C) or the Account Number (column B) changes. The totals should be under Columns D and E only. But I don't want it to duplicate the results when both the product and the Account number change. Beginning data: ...
View Replies!
View Related
Dsum Based On Multiple Criteria
Ihave the following headings in a database CSAMeeting DateMeeting typeCompanyInhouse Products/FundsJV Product 1JV Product 2VenueSales Manager2nd Manager (JP, KMcI SS)Inv. ManagerFilenote IssuedWorking DayNo. of AttendeesCancelledPack RequiredCompliance Required DivisionReason for no ComplianceDate sent to CompliancePack/Literature UsedPack Path i want to use the Dsum to produce the total amount of managers met for a cvertain sales person for a certain type of meeting. I trued thr following but it is returninga #value =DSUM(A27:V1028,13,AND(3=$BH3,9=BI$2,120)) where 13 applies to the number of managers column 3 is the meeting type 9 is the managers name 12 ensures that its only counted if teh meeting note is done.
View Replies!
View Related
MAX Date Based On Specific Criteria
I need to determine the MAX date in a column based on specific criteria. Column A6:A500 = Task Names, Column N6:N500 = Dates (there are some blanks). What I need is to determine the latest date in column N6:N500 based on which task I choose from column A6:A500.
View Replies!
View Related
Add To Current Date Based On Criteria
I am trying to get a function to return a date based on the string in the preceding cell. If the cell, say A1, has the string "Before 12:30", i want the cell B1 to put the date 5 days from now in the cell. If there is any other string entered then 6 days from now. I also need it to include the networkdays function. Here's what i've got, messy i know but functions aren't my strong point. Also would i save file size if i did this in VBA? ....
View Replies!
View Related
Extracting Rows Based On Date Criteria
I have a sheet of data, and i need to extract rows that contains dates containing 5 in them. Eg, 5 Jan, 15 Jan, 25 Jan... 5 May, 15 May, 25 May and copy them into a new sheet. Is there a faster way to do it via VBA rather than manually extracting them?
View Replies!
View Related
Multiple Criteria Lookup With Date Range Match
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
View Replies!
View Related
|
TRACKER
