ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10
Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.
Is there someway to do this with a vlookup or maybe match index?
Is VLOOKUP suitable for if one wants to lookup a value from a defined range based on two assumptions?
Enclosed is an example file.
I want to lookup the values in sheet1 in the defined range in sheet2 and write enclosure and density to sheet 1
what method to use?
nicolaiBook1.xlsx
I have written a snooker scorebaord spreadsheet which keeps a history of highest scores and highest breaks, but am unable to find a way of showing these key values in a table.
Attached is a cut down version to demonstrate the problem. If you can provide a solution it can be either in formula or code.
ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10
Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.
Is there someway to do this with a vlookup or maybe match index?
I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/2008-1/15/2008 and $20 for 1/16/1008 - 1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008 - 2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
I am not sure if the VLookup or the If function will be the best way to perform this tast but I think it is they way I would want to go.
I have attached a sample workbook of what I am working with.
Please refer to it.
On sheet 1 I named the table array Ear_Tag.
This is information that is provided to me from the farmers.
Note that there are sometimes more then 1 pig with the same tag number. The only way to tell them apart is by the Test Date.
On Sheet 2
Is where I record the test results.
I dont want to have to look up each pig after each test and fill in the rest of the cells manually. I would like to have a look up of the ear tag and the test date to find the correct pig. Then each of the cells will fill in the correct information.
My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
Example: give me the value in cell e1 if cell b1 matches AND
File 1
A B C
1 H12377 03/05/2013 123
2 H12377 03/27/2013 276
3 H32389 05/03/2013 335
File 2
H12377 03/27/2013 _________
how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?
I have found that excel can be the best thing ever and also a nightmare when you are stuck on something. I really apprecciat anyone who can help me out.
In an excel workbook i have a sheet that has rows and rows of Sales Leads.
In column A i put a letter "C" if they have turned into a client or an "X" if the sale was not made.
I am looking for a function that will look at column A and when it sees the letter "C" then copy the entire row across and put the row into sheet 2.....
I am trying to retrieve data from another file using the VLOOKUP function however this is only to happen depending on if any of the 3 items of data appear in column 8(H)
OLY
OLY - QUO
OLY - PRO
I have the following and know this is not correct
Sub BlockAllocationsVlookupAll()
Dim x As Long For x = 1 To 65536
If InStr(1, Sheet1.Range("$H$" & x), "OLY") > 0 Then Sheet1.Range("$I$" & x) = Sheet1.Range("$I$" & x) & "sometext" End If Next
End Sub
I know the above doesn't do exactly what needs to be edited to include the Vlookup below
=VLOOKUP(A21,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536,9,FALSE)
The other issue is that the cell the VLOOKUP points to first will also change due to the varying length of the report
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
My data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub
I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
see attached workbook.
I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.
1) The name is in the list
2) The code in the list is one from the codes tab.
I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status %
S1 75% and higher
S2 42.8571% -74.9999%
SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
My spreadsheet looks like this:
Column A: Individual
Column B: Not used in formula
Column C: Not used in formula
Column D: Location
Column E: Duration in Minutes
Column F: Pay (Column E)*(Column G)
Column G: Formula
(simplified version listed below)
[Code].....
To be clear of what I'm trying to achieve:
When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.
My question is:
How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?
I had tried adding these lines (with their corresponding closed-parentheses at the end)
[Code]....
But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.
I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))
but it's returning zero values regardless of the criteria being met or not.
I have a table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies View RelatedI want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
I am trying to sum numbers based on three counditions.
My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*
The yellow cell contains the formula I am using.
I currently have this equation: =AVERAGEIF($B$2:$B$900000,">="&L5,$C$2:$C$900000). Where L5 = 100. I'm interested in having the formula averageif B column value is between L5 & L6. Where L6 = 200. So I want it to find all values in B$ that are between >= 100 but < 200, and then average their C$ counterparts.
View 3 Replies View RelatedI'm having some problems getting a simple Do While loop to run. Essentially I'm trying to get it to run as long as one of two conditions is met, so I used an Or function in the loop condition. This then produces the "Run time error 1004: Application defined or Object defined error" error on that line.
I'm certain my syntax for the two conditions is correct (one is a simple check value in a cell, the other is check the value of a VBA variable) so it would appear the error is caused by the inclusion of the Or function. So I guess my question is this, have I done something wrong? If so what and if possible why is what I did wrong (I like to understand these things)? Is this something that has to be done in a different way or even is it something that cannot be done? I'm using excel 2003 on XP SP3.
way to return a value from a data table by specifying 3 conditions to be met.
Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)
I want to be able to return the time value, based on track, distance and class values.
My book is telling me that I can't use multiple conditions with a SUMIF
statement but other sources are telling me I can. Either way, it's not
working!
My situation:
Look at data in $V$2:$V$144 (range = "group"). If cells in "group" =
"Broth" then look at $Y$2:$Y$144 (range = "bldg"). If "bldg" = "1" then sum
corresponding cells in $R$2:$R$144 (range = "fcst").
This is one of my attempts - feeble...
=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))
I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement:
= IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER"))))))))
But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula
My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.
I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.
I have attached the sheet in pk zip format.