Using 2 Criteria To Return 1 Sum
Jun 16, 2009
I have a a stock list with the following headings: Location, Part Code, Qty this is the source data for my next spreadsheet. This is baically a total stock sheet.
I need to extract data into my second sheet looking by part code .
I would like the formula to lookup against my master stock sheet and bring me back a sum of the total stock for part code ABCD, which is in location 1.
My finished speadsheet will have 21 products over 10 locations, and the master stock sheet will be amended daily which will hopefully update this summary sheet.
View 9 Replies
ADVERTISEMENT
Jun 3, 2008
way to use three criteria - date, account, method - to identify values in a column of deposits to sum.
The spreadsheet consists of the following:
The first colomn is a mixed list of dates in different months deposits were made.
The second column is the account the deposits were placed in.
The third column is the method used to make the deposit
The fourth column is the amount deposited.
What I'm trying to achieve is
1. to sum all the deposits made in a particular month by the different methods into a summary cell for each month
2. to sum the amounts deposited into each account each month into a summary cell for each method
I did try to copy a section of the spreadsheet using the html maker but couldn't copy it.
View 9 Replies
View Related
Jan 11, 2009
Rather than attempt to describe my problem here and risk cofusing people on what I want to achieve I have put a diagram together. I think this is the best way to illustrate my problem.
Diagram is available here
[url]
There is also a copy of the document available here for any body willing to take a look.
[url]
Please bare in mind I need this doc to be compatible with the 2003 version of Excel.
View 14 Replies
View Related
Mar 3, 2009
i have a file (attached) with a price for zone and weight. I want to look up the zone, look up thge weight and return the correct price.
View 4 Replies
View Related
Jul 10, 2012
A
B
C
D
2
Plan 1
Plan 2
[Code] .....
I have two drop down lists. Drop Down 1 contains the values in column A. Drop Down 2 contains values in row 2.
Based on the two selection I need a formula in D7 to find the intersecting value.
Example: Drop Down 1 selected "Dog" Drop Down 2 selected "Plan 2"
I'd like value "$12" to automatically appear in cell D7
What is the formula I should enter into D7?
View 3 Replies
View Related
Mar 26, 2013
we would like to get results from a formula that looks at several cells and provides the cost for a product.
Example
If we choose
Cell A3=Transport (from drop down list)
Cell A4=Entrance Facility (from drop down list)
Cell A5=Bandwidth (from another drop down list)
returns the cost for this product in cell A6
We would also like to restrict the lists to the different catergories: if transport is selected you only have the option of 2 of 5 facility types that will work with transport products. Do I need to separate my lists?
View 1 Replies
View Related
Jul 3, 2014
In range B1:C5, I want to return the value in column B to column A if column C is empty as shown in the following table.
Coml A Coml B Coml C
S1S1
S3S2 89
S4S3
S4
S5 67
View 2 Replies
View Related
Feb 22, 2007
I have 2 rows of data and want excel to find the number of times that a number appears in the first row and then return the value of a cell in the same column but in the second row of data. I need it to repeat this until all matches in row one, and their corresponding number in row 2 have been found and then add all the results from row 2 together to give a single numerical answer. I have tried the ' lookup' function but this only returns the first number that matches the criteria and does not continue to find the remaining matches.
View 7 Replies
View Related
Apr 1, 2007
I have 3 named range columns to query and a fourth from which I wish to return a value
Column 1 is called DateOE, column 2 is called NameOE and column 3 is called RunOE. The column from which I require the value is named ConcOE.
I have the following formula:
=IF(AND(DateOE=28,NameOE="Wayne",RunOE=1),ConcOE,"No Data")
My logic dictates that the formula should return whatever was run by Wayne on run number 1 on the 28th day from the values within ConcOE or return the value No Data.
The run numbers are unique, which is the identification key.
Every time I try it out, I have a #Value returned and if I convert to an array, the value no data is returned, despite the fact that I know what value should be retuned.
View 9 Replies
View Related
Feb 10, 2008
I have the name of an employee in cell B5 that I choose from a list. In cell L18 I have the result of the quality monitoring for this employee for a certain task. Now I have a seperate spreadsheet where I need to come up with individual performance scores. What I need to do is return value in L8 if B5=the agent I'm looking for. This also means that I would need to be able to do this on multiple cells in the same sheet. I've tried: =IF('[Workbook.xls]Sheet1'!$B$5=Name Employee,'[Workbook.xls]Sheet1'!$L$8,0) But I get a #name error and being pretty new to this I have no clue what to do next.
View 3 Replies
View Related
Aug 10, 2014
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
View 3 Replies
View Related
Feb 3, 2011
I've a two column dataset with one column for person name and one column for sales.
I'm trying return the nth largest value where the person name equals the value in a referenced cell.
My dataset may grow or shrink over time so I'm trying to avoid array formula.
Basically I'm looking for something like the below that actually works.
View 5 Replies
View Related
Dec 19, 2012
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
View 1 Replies
View Related
Jan 10, 2014
I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER
View 2 Replies
View Related
Dec 8, 2009
If I have a 'key' value in a cell in one sheet, i want to use that value to find the cell in another sheet containing the 'key' and return the row number of the cell, if more than one value then I would like to be able to loop through all the rows containing that 'key' value returning the row number of each hit, kind of a programmatic version of vlookup?
View 8 Replies
View Related
Aug 13, 2008
i have a spreadsheet like the following
A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan
and so on...
So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View 5 Replies
View Related
Jan 16, 2009
I have a spreadsheet with multiple columns and rows of data. I want to be able to type in a criteria and all the rows containing the criteria are called up. For example
Col A Col B Col C
Row 1 Apple Fruit 12
Row 2 Banana Fruit 15
Row 3 Carrot Veg 13
I want to have a cell on another sheet in which I can place a criteria, eg Fruit, and then the entire row 1 and 2 are displayed on the second spreadsheet.
View 9 Replies
View Related
Jun 2, 2009
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies
View Related
Jan 26, 2010
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.
Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View 2 Replies
View Related
Dec 7, 2011
I have a table with the following headers: Customers, Location, bill number, date of bill, number of days you have untill you must pay the bill, the rest are not important
I need to return a list of the bills from the last six months, in which the customer has been granted with days until he must pay the bill(there are some with no granted days).
The table headers are translated, they are not so long.
I need to do this until friday.
View 1 Replies
View Related
Sep 25, 2013
I would like Excel to return me a value based on many criterias in the same worksheet.
E.g
A
B
C
[Code].....
QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.
View 1 Replies
View Related
Feb 15, 2010
I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.
To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber
Example
Row
1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00
View 5 Replies
View Related
Oct 27, 2006
if Cell F42 contains 50%, then my fomula returns "wrong"
=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))
how do I get the formula calculate correctly?
View 7 Replies
View Related
May 22, 2014
I have attached a very simple model of a much larger BI report that we use. I have written a DSUM that returns the correct result in all cases other than when one of the criteria columns is blank. When one or more columns is blank, the result returned is 0 whereas I need it return all data (for e.g. if you remove "sains" from cell B2, I need it to still return data for person "b", "c" and "d" (i.e. 51 for Mar14)).
Attached File : DSUM (2).xlsx‎
View 3 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 Replies
View Related
Jan 31, 2014
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time.
The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following:
Shift 08:00 - 17:00
Tea Break 10:00 - 10:15
Lunch Break 12:00 - 12:30
Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
View 2 Replies
View Related
Feb 14, 2014
I have a table that I would like to search to return all the values that meet 2 criteria entered by the user.
I have 3 columns - Role Name, Skill, and Skill Level - I'd like to be able to enter the skill and skill level and return all the roles that meet the two entered criteria.
At the moment I have an array, but it only returns the first value from the Role Name column that it finds.
=INDEX(A2:A100,MATCH(1,IF(C2:C100=G4,IF(E2:E100=H4,1))),0)
Where the A column is the role name, C is the Skill, and E is the Skill level. In cell G4 the user enters the skill to be search, and in H4 the level required for the skill (a scale of 1-4)
Is it possible for the formula to return all the values in column A that meet the criteria entered in G4 and H4?
View 7 Replies
View Related
Oct 15, 2008
I am trying to combine a If(is(numbersearch combination with a Frequency formula , i am not using sumproduct as want to return a complete total if criteria is blank.
When a certain validation is entered it returns the number of individual number combinations in a column ignoring duplicates (columns are number only not text)
View 5 Replies
View Related
Jun 19, 2009
I have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:
If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue
The problem is when B3 is greater than 0 it does not change the cell colour to Blue.
B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue
See attached for spreadsheet with conditional formats
View 3 Replies
View Related
Nov 10, 2009
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
View 2 Replies
View Related
