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.
View 2 RepliesHaving trouble getting a formula to work that will lookup a site number and count the number of times the site has a particular type of visit. For instance:
Site # Visit
1001 Active
1001 Active
1001 Discont.
1001 Screen Fail
1001 Active
1002 Discont.
1002 Active
1002 Screen Fail
1002 Active
I want a formula that will return the number of active instances for each site, like:
Site # # of Active Visits
1001 3
1002 2
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies View RelatedI am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies View RelatedIs it possible to set up a lookup function with two lookup values? For example, say I have a list of items such as:
1 A 14
1 B 22
2 C 84
4 D 25
I'd like to have the lookup go to the above table and find the number 1 and the letter B and return 22. I can't seem to visualize how to make this work.
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 am trying to count data using several criteria
1.Need to add data from for a certain category, say "blue" + data during a certain date but exclue the ones with zeros
My formula using arrays look like this, but it is still counting data with zero in the cell as an item
=count(if(A4:A400(text by category)=”blue”,count(if(O4:O400(date)<”04/01/07”,count(An4:An400)[Actual data],-(countif(An4:An400,0))))))
Im trying to return the number value of how many contracts have been signed in a particular month. So in my example I want to look pick out say "IM" and check to see how many of those types have been signed in say "Jan-09" and return that as a number. Have tried various mixes of count, sum, countif and sumif but dont seem to be getting anywhere.
View 4 Replies View RelatedI'm trying to extract the number of times specific keywords are used in Sheet1 and display that number on Sheet2.
For example:
Keywords to be used
ni = not interested
appt = appointment
na = not available
If ni is used 10 times, appt is uesd 2 times and na used 6 times.
I want to be able to see those numbers on Sheet2 next to the keywords.
e.g;
ni = 10
appt = 2
na = 6
I need to add the total of staffs hours worked for one day, but the problem is that I don't recieve the data as hours but as symbols(letters of the alphabet) representing time worked. Eg "A" is 3.5 hours, "B" is 4hours "C" is 4.5 hours ect, ect.
In the example the top table is a one month time sheet for each staff and there working shifts. The bottom table is the part that I need a formula for. I need a total for each symbol for each day so I can total the hours at the bottom where it says total hours. I have given an example on how the bottom table should look when the formula is completed.
=SUMIF('[Weekly Sheets.xlsx]W1'!$A$8:$A$10000,$C$1,'[Weekly Sheets.xlsx]W1'!$K$8:$K$10000). It works fine but I need it to also lookup cell B8 if B8 has text named "ABS" dont count whats in cell K8
View 2 Replies View RelatedCan I get all the information from Employee & Benefits tab to populate in the 401K worksheet. All information is on these two sheets except status, plan entry date, and 2009 compensation.
True under Employees tab means that a person is still active and I would like for that date to populate the 'Hire Date' column; whereas, 'False' represents a person is termed and I would like for the false date to populate under termed. Can you use the social security number if its a V Lookup because I have thousands of rows and multiple people with the same last name.
How do I write a code for the following Excel formula:
=LOOKUP(E20,{0,2501,5001},{"CatA","CatB","CatC"})
I tried the following but not working
I would like to do is reference a table of data using the Vlookup function but add all of the results together. I have a table where I keep track of orders being distributed, and in an inventory sheet I want it to reference that table based on the serial numbers entered and total the results.
View 3 Replies View RelatedA B C D E
1 DAY 1DAY 2DAY 3DAY 4
2 AAA1638
3 BBB7325
4 CCC3847
5 DDD5562
This is what I have. Now I want to see, for each day which has the highest value. so it should return BBB for day 1, CCC for day 2, DDD for day 3 and AAA for day 1.The formula I am using:
=lookup(max(B2:B5),B2:B5,A2:A5)
=lookup(max(C2:C5),C2:C5,A2:A5)
=lookup(max(D2:D5),D2:D5,A2:A5)
=lookup(max(E2:E5),E2:E5,A2:A5)
Tell you what, it is giving me correct result for column B,C,D but not for E. The formula for column E should return AAA, but for some reason it is returning DDD.
I just wanted to use lookup function in my code. But I had to re-write my for..next statement 8 times. How can I state it in one single lookup function used in a for..next statement. Here is my code
Sub iteration(r)
Dim i As Integer
Dim j As Integer
Dim x1(4) As Double
Dim arra(8, 2) As Double
Dim arrb(8, 4) As Double
r(1) = Worksheets("sonuc"). Range("j4").Value
r(2) = Worksheets("sonuc").Range("j6").Value
Dim myRange As Range
Set myRange = Worksheets("giderler").Range("b6:b11")
x1(1) = Application.WorksheetFunction.Floor(r(1), 0.5)
x1(2) = Application.WorksheetFunction.Ceiling(r(1), 0.5)
x1(3) = Application.WorksheetFunction.Floor(r(2), 0.5)
x1(4) = Application.WorksheetFunction.Ceiling(r(2), 0.5)
For i = 1 To 4
I have 2 worksheets, 1 containing a list of 5000 products and 1 containing a list of 3 columns with classifications in them (about 300 classifications). for example worksheet 1 has 5 products
Coke
Volvic
Mars Bar
Cornflakes
Bags Maltesers
The 2nd worksheet has 3 columns with the following info
Column 1 Column 2 Column3
(Catagory) (sub Cat) (Sub Sub Cat)
Minerals Soft Drinks 2lts
Minerals Water 1lts
Sweets Choc Bars Std Bars
Sweets Bagged Lines Std Bags
Grocery Breakfast Cereals
I want to be able to add a classification to the products in worksheet 1. For example to be able to choose a dropdown that has a list of each classification
and after coke select from the "Catagory) drop down, then select the "Sub Cat" drop down and then select the "SubSub Cat" dropdown.
Product Catagory Sub Catagory SubSub Catagory
Coke Minerals Soft Drinks 2lts
Mars Sweets Choc Bars Std Bars
As I said I have to do this manually for about 5000 items so I need to be able to do this as fast as I can. Can you use the lookup fuctions or something like that?
how to fix the REF error in this formula. =IF((OR(G3="Hour (Truck)",G3="Hour (Truck + Dog)")),(VLOOKUP(G3,Rates!$A$186:$B$187,2,FALSE)),(IF(F3="Hanson",3.5,(IF(F3="Lakes",10,(IF(F3="NO",(VLOOKUP(E3,TonnesRates,MATCH(B3,Rates!$B$110:$D$110),FALSE))))))))). I have tried to attach the file with the minimal information in it to make it small enough but I can't get it below 48kb.
View 7 Replies View RelatedLook up a cell value from another workbook and count the match
View 9 Replies View RelatedI have a problem getting an adequate formula for the following.
"Sheet 1"
Column A.............Column B
Employee Code.....Distinct Count
ZZADW...............Formula Result
ZZALM................Formula Result
ZZALS................Formula Result
ZZANG................Formula Result
"Sheet 2"
Column A............Column B
Employee Code.....Account Number
ZZADW...............1SSS
ZZADW...............1STT
ZZALM................5GHL''''''''
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
The following Lookup-function does not work properly. Ay idea why? My Input in E13 is months.
=LOOKUP(E13, {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},{"012","001"," 002","003","004","005","006","007","008","009","010","011"})
I have store numbers and store volume groups (one letter indicating their sales volume). This is sorted in somewhat of a table - the volume groups are in row 4 going horizontal, the store numbers are under their respective volume groups going vertical.
I have all store numbers listed vertically on another sheet, starting in A2. I want to search for the store number in A2 within the volume group data and return the appropriate group header when it's found.
A
B
C
Ap1
queens Carrots
Goja Apple
Ap2
fujja Apple
fujja Apple
Ca23
Goja Apple
queens Carrots
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
In my spreadsheet, one cell in every row has a string that contains the invoice number. I would like to get the invoice number in a cell of it's own. The problem is that different vendors use different lengths of characters for their inv #, so I can't do a one size fits all MID formula.
What would be the best way of getting the invoice number?
I'm trying to set a range to use in a Lookup function but it's not working. I've done this other times with no trouble and I don't see why it's not working now.
If Target.Row > 2 And Target.Count > 1 Then
For Each c In selection
If c.Column = 11 Then
' confirm that Activity ID is in the Reference file list
Set Lookup_Vector1 = Worksheets(c.Worksheet.Name).Range("Activity_ID")
Error occurs on the Set Lookup_Vector1 line, Run-time error '1004': Application-defined or object-defined error.
The range Activity_ID is defined in the current worksheet but exists in another workbook. The c.Worksheet.Name correctly gives the name of the worksheet where the range is defined.
Look at 6 weeks of data, using "Tue" as the search parameter. I need to find the last 3 tuesdays (from current cell) and then sum the corresponding values in the cell I designate.
I'm fluent with LOOKUP and already have it finding and summing last few Tuesdays and corresponding sales totals, but I need it to only back THREE Tuesdays consistently, not 2 or 4, which it is sometimes doing now??? How do I force it to look back at "tue" and when it has found three times, it knows to stop and not consider any other Tuesdays it may find further back in the data?
i use the formula below in a wb. When it runs its searches col A for what ever is in H36. Is there a way to make it search col A & B for what ever is in H36?
=IF(ISNA(VLOOKUP(H36,'PRICE-GAS'!$A$149:$N$6000,5,FALSE)),0,VLOOKUP(H36,'PRICE-GAS'!$A$149:$N$6000,5,FALSE))
Im just setting up a spreadsheet for structural timber design calculations. One of the requirements for this spreadsheet is to look up a Zx value from a table. Once the Zx value is found it returns a size of timber from another column. I have managed to create the function so that it looks up the Zx value and returns the size. However, if the function cant find the exact Zx value, which will usually be the case. The next lowest Zx value is used. Is there a way i can edit this function so that it returns the next highest Zx value instead of the next lowest. At the moment this is the code i am using.
= LOOKUP(Q92, 'Zxx Table'!E5:E172,'Zxx Table'!C5:C172)
Where Q92 is the value to be looked up. E5:E172 is the column of values to be searched. C5:C172 is the column of values where the result comes from. In addition to this just to further complicate things, can it return say the next 5 highest values? If a lookup function cant be used could you tell me another way that i can achieve this.