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.
I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.
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 -
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!
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.
Basically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.
I am trying to use a "=COUNT(IF(..." formula to count the number of hourly employees that have start dates older than 3 years. Column A has the start dates and column B has the salary status (S/H). =COUNT(IF(AND(A2:A1000<DATE(2004,1,1),B2:B1000="H"),A2:A1000)) This formula returns 1 no matter what I change the data to.
Want a single count of multiple columns based on the columns selected value. Data is in text format.
Have tried multiple COUNTIF statements and have tried using pivot table (Excel 2010) both only give me total counts for all. I think I need an OR statement somewhere, but not sure where?
In other words, if a single record has an "any" in the any fields or a "yes" in the yes fields, I want to to count that as one record.
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.
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4 89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run. 2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L 3. In Column D, I want to specify 3 criterias namely A, B and C 4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo 5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
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.
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
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G) 2.) Multiply the % by the Revenue (Column H) 3.) Sum up the total for each of the names (Locatedin Columns B, D, F) 4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
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.
I have two ranges of data and want to count the unique number of times the values in column A occur if they have two values in column B. For Example:
ColA ColB 333310143333306344441014444430635555161246666101466661612466663063
I want to count the number of times value in ColA has the value 1014 AND 3063. My intention is to create a table that cross references the two
10143063161241014331306333116124112
I searched and found answers where multi criteria were in multi columns but I can't seem to find a solution to this means of find the unique count in ColA.
