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

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).

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.

I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.

Trans Type 1 Return 2 Return 2 Return 3 Exch 4 Exch 5 Return 5 Return

Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like “AC”&Rw , “Q”&Rw, &Rw, Rw being my incremental variable used to go through the rows.

I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.

I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).

I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.

Period Cost Center Account Item No PO No Amount Test

As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?

My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy

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

I've got an array of data about 30x1500 cells in size, I want to use this data in a table I'm making on another worksheet.

Normally I'd just use VLOOKUP and this works great in most cases but I want to do something a little bit more complicated. Basically I want to (in normal English) have excel look at the table, find a cell that matches criteria in this data array, then compare to another cell on the same row in this array. If the criteria for these two match then display the data from another cell on the same row. The criteria for matching the first cells would be an exact value, both being strings of text.

I've tried using a couple of nested VLOOKUP and IF statements but failed .....

I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.

HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.

C is the part number column in the schedule K is the date row in the schedule ....

Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.

On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.

Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.

As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.

I am developing Spreadsheet that you will enter a list of parts that you need. it will have many columns but only 4 are vital to the check. this will be generated for each project.

With each part i want to then look up in another file that is a list of all the possible parts you can have with there cost. this file will be standard for everything and it will be updated from time to time.

now not all the criteria to look up will be exact matches.

here is an example

code temp/pres rating diameter material GLV 4500 15 16Mo3

in the other file we will have multipu entries for GLV with different values.

For Code i need it to match (there are lots of codes even including variations like GLV.) For Temp/pres rating i need it to be equal or higher to the value For diameter i need a match for material i need a match.

so as you can see from the list above there are 2 options. so then i want it to return the cheapest.

that is step one.

step two is to repeat it for multiple list of parts.

[Date] [Category] [Currency] [ExchangeRate] [.....], etc. 1-3-09 A USD 0,8 1-6-09 A EUR 1 1-7-09 A USD 0,7 1-8-09 B USD 0,9 1-9-09 B USD <formula>

I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date.

Which formula and what syntax should I use to have this done?

I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.

The only unique criteria I have to do this are customer surname and customer postcode.

In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.

If they do I'd like it to display "match" if they don't I'd like it to display "no match"

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 attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.

The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.

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.

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.

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, Id 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.

Ive 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 doesnt 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.

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