Multiple Criteria Count Between 2 Dates & Other Criteria
Jul 30, 2008
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 -
I am trying to compile a count of dates using a numerical reference which adds x consecutive dates onto the initial start date based on the number in a cell.The dates added cannot fall on a weekend.
The start date for each calculation begins in D4 with the corresponding number of days to add onto this date in K4. In the example below 8/11/2007 -- 2 means that the 8th and 9th of Nov get one added to their count. As the next row also has 9th Nov as a start date one more is added to 9th Nov. As the 10th and 11th were a weekend they are skipped and the count starts again from the next Monday. I have enclosed a simplified worksheet with some sample data.
need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)). So now I just need it broken down by training type per month. How many x's in each column for February as an example.
Here 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
if possible, with a Excel formula. Assuming a column (M7:M200) has this information:
John Mary Lisa Mark John Albert |_Empty _| |_Empty _| |_Empty _|
I would get the sum of the cells except "empty", "John" and "Mark". The result would be: John: 2 | Mark: 1 | Others: 3 (My only interest is the formula to get the result "Others: 3") I tried with this formula: = COUNTIF (M7:M200, "<> John") but remains exclude "empty" cells and "Mark."
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.
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.
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.
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..
I have a bunch of cells that i want to count in a table based on 2 criteria. The first would be checking whether or not it matches a certain text which i can do. Now i have a column for "Completed Date" and "Deadline Date". Each deadline date is different. I know i need some sort of array function in there to compare all the completed dates vs deadline dates but i just can't think of it. This will be the second 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.
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.
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.
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
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
i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.
Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank. 2) Column E (Status) has to be equal to either "Completed" or "Successful". 3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
I am currently using the COUNTIFS formula to get the information I need when the workbook is open but I would like to be able to pull these counts without opening the workbook. Here is the formula that is working correctly while the workbook is open:
=COUNTIFS([TT_Output_All.xlsx]Sheet1!$C:$C,D$1,[TT_Output_All.xlsx]Sheet1!$F:$F, "2014",[TT_Output_All.xlsx]Sheet1!$I:$I,$A17,[TT_Output_All.xlsx]Sheet1!$L:$L, "production: am owner")
Convert this formula to an array that doesn't require me to open the workbook to get the counts.
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.
I want to count cells that contain specific text but multiple criteria. For example in range B4:B12 I want to count the cells that contain the word 'daily' AND the word 'weekly'. Each cell in the range contains one word.
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
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?