I have a database where I need to count the qty of such item but it has to comply with two requirements. It will count only those that are similar to one of the cells but if the number is the same, not taking in count.
Example:
ItemLiablibity354A354A543A345A567A456B567B
So, I want to count all the items but only the one that matches the A on the column "Liability" but if it is repeated, not counted.
so the resul must be: 4 as one number is repeated.
I am creating a formula. The formula needs to be something like this:
[Code]...
Basically, I need the formula to read the value in cell A and if it is a "1" then the value in Cell G will remain, all other values in cell A will erase the values in cell G.
Basically We have a column with our part numbers and a column with the vehicle applications which contains the years, make, model, and engine.
Most of our part numbers have multiple applications that they work for.
The vendors system only uploads unique part numbers, so I need to find an easy way, if posable, to combine all the applications that use the same part number in one cell next to it's specific part number.
I don't know if I should be messing with formulas, pivot tables, VBA...?
Need to summing a column if it meets two requirements. First it has to be (column A)type1 A and (column B)type2 W. if type1 and type2 are true, sum column C. I also attached a file that explains what i'm looking for.
The logic is that I want the cells to be highlighted and have a border drawn for them as shown in the attached sheets (I have used record macro which will do this for rows 39 and 40 when the click button is clicked). The highlighting should start at the row number specified, stop once a merged cell is reached, skip the merged cell, continue highlighting, stop once a merged cell is reached, skip the merged cell, continue highlighting and so on and on until the last row (which is user specified) is reached. The first row after the merge cells should be highlighted in grey and yellow.
In Column A I have vessel departure dates In Column B I have Final Destinations In Column D I have vessel arrival dates
I am trying to get the result of column D based on matching A & B information. My dilemma is that if I have vessels with the same departure dates going to different destinations how can I retrieve the arrival date? I've been trying "vlookup" and "index/match" to no avail.
I've been asked to produce a spreadsheet that monitors the time it takes to answer queries from partner companies. Ive attached some of the spread sheet that im struggling to work out. I would like a formula that will total the number of days it takes us to answer the queries per month for each company.
Example - For January Compnay A 8 Days Company B 7 Days Company C 13 Days
I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
I would like a cell to show either OK or No depending on the requirements of three other cells as below
G4 is the cell to show a value. For OK a date in G6 must be more than todays date, a tick must be in F6 and a date in D6 must also be more than todays date.
I can get G4 to do each section individually i.e. (A4 has todays date in it, updating automaticaly)
I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, the name on this sheet will change daily, so you cannot use a "named" sheet in your macro. The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The data comes in such as:
Based on the FIRST TWO characters in the first column, I need to copy the data to the specific sheet. I would assume a simple LEFT() function would work for this. Here are the requirments for each sheet:
Esky - "MZ" or "51" Indy - "IZ" or "Z" or "21" Gfld - "PZ" or "22" Other - Anything else
With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.
This is a two part process:
Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")
Here is part one
Code:
Sub Part1() Sheets("Open PO by Vendor").Select Sheets("Open PO by Vendor").Cells.Clear With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _ , Destination:=Range("$A$1")).QueryTable
[code]...
Part one works perfectly.
Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.
I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.
As follows:
In particular: *E3 from Part 1*, etc.
Code: "SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _ , _ "tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _ )
Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and
Change
Code: WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"
to the value of that nested if table
The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.
3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.
What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.
I would like a formula in cell B13 which would look at the range B2:B10 and count how many are in between 07:45-08:00. In cell B14 I would like it to look at the same range and say who is on shift between 08:00 and 09:00, and so on. The reults should be the same as I have typed in cells B3-B29. Is this poosible and/or is there an easier way if there is?
I have one long column filled with the names of people. I need to count the number of people that show up once and the people that show up more than once. So I am looking for any name that shows up more than once or just once and not the specific name of the person. I can use two different formulas for each result I am looking for.
For instance(column): Patel Patel Patel Smith Myers Kaplan Jones Jones
I need to come up with an answer that shows that 2 people have their names show up more than once and that 3 peoples names show up once.
Right now I have a column of about 300 rows where I want to count how many actual names are in the column. I thought that COUNTA(c2:c313) would work, but it returned 312, while Im expecting it to return about 10.
I think that the reason is because all of the cells in this column are pulling data from another sheet using this formula: =IF(Clients!I299="","",Clients!I299). Most of the data are blank cells
Since of the rows in this column are empty, so I was wondering if I perhaps there was a function such as "DONTCOUNTIF" which would count the cells which didn't have a certain value, in this case "".
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
I have a range of cells that may contain several different abbreviations (text groups). I have used the COUNTIF function with * place in front of text to count cells that contain the listed text eg =(COUNTIF(G6:G39,"*HWD")) which has worked well and returns an accurate count of the cells that contain HWD. I have tried to do the same thing for a COUNT function that contains an IF function and no count is returned. When I remove the * from the formula a count is returned for cells that contain only the HWD text and not cells that contain HWD and other text. {=COUNT(IF(P9:P69="MCAW",IF(G9:G69="HWD",J9:J69)))}.
I am creating a spreadsheet to keep track of money and grouping them into categories. I have a column to group a purchase into a category (column E) and the amount spent in column F. I want column T to add up all of the purchase that were made according to the category in Column E.
What i am trying to do is count the number of rows that contain data.
It will always start in A7 and when creating the macro i hit control-shift-down arrow.
There will be a differnt number of rows for each data set. When i ran the macro it kept the result of the first data set that i used to create the macro.
I have a 2500+ line document with different years indicated in column D. How would I use the count (??) function to count the number of occurances for 1998, 1999, 2000, etc?
I am trying to setup a formula that does the following:
Sheet 1 contains the formula. It performs a COUNT on a column on Sheet 2 and dislpays the result on sheet 1. I need to tell Excel to omit from the COUNT list, all entries containing the word "Free".
Inm other words, i need a count performed on column A of sheet 2 but the result must ignore all entries within that column A that contain the word Free in it.
I have two columns on sheet called back1! if a number is put in a cell from P5 down to P40 and a corrosponding number matches in K5 to K40 I want to be able to count it as 1.
So if a number is in k6 and a number is in P6 it counts as 1.
*ABC53TypeCost$Bought54Orange3Friday55Orange5Sunday56Apple4Friday57Orange5Friday58Orange4Sunday59Apple4Friday60Banana3Tuesday61Orange4Sunday Excel tables to the web >> Excel Jeanie HTML 4
Now, i want the Sum and Count of Orange which is bought on Friday
I have roughly 150 cells I am trying to count, some blank, some not blank, some with numbers. All are in the same column. I want use a simple function that sums the total number for me, say from (A2:A153). Answers?
If cell D3 has the text string "RQS" and cell D2 is colored green, I want to count as one. The formula needs to apply to a range of cells. The end result being I want to know how many green cells are delineated at RQS.
