Return A Value For Two Conditions
Mar 27, 2007
I need a formula that will return a value if two conditions are meet. This is probably pretty easy but i am drawing a blank.
I have two columns B & C and in those colums there are numbers from 1-5. And in column A there is a number.
I need the formula to look in column B&C and if the a condition is meet it will return the number that is in column a
A B C
1 2 2
2 1 3
3 3 3
4 2 3
For Example:
I need the formula to look in column B&C where the value is 1 for column B and 3 for column C and return the value in column A which would be (2)
View 9 Replies
ADVERTISEMENT
Sep 9, 2009
I have 3 cells,
A1 = " a name"
A2 = " a time"
A3 = " a day ie Thu"
I am trying to get a formula to look in B1:B100 for the cells that match A1, then in those cells look in C1:C100 for cells thant match A2 then in the cells that meet both those look in D1:D100 for the ones that match A3. Then if there are any cells that meet all 3 matches return the value "a" if not return a blank.
View 9 Replies
View Related
Aug 9, 2012
Formula that will return the value in the fourth column. It will find all instances of that person on a particular date and average them together.
Date
Name
Value
Average of that person per day?
7/31/12
Billy
5
8
[Code] ..........
View 4 Replies
View Related
Mar 24, 2009
I'm trying to create a formula that that returns an average value if two conditions are met. No luck here.
I need to be able to get to - Brand = Blue; Value = 2; X Count = average - from the following information. The average is 17.5
BrandValueX CountY CountZ CountGreen1213152Green1405595Blue3107686Red1164965Green21098108Green33077107Blue21796113Blue13970109Red3144963Blue1107787Red31096106Red24663109Blue2165773Red21888106Green31910
View 9 Replies
View Related
Dec 22, 2011
if color(white),pattern(6" tile),finish(gloss),width(>36") & height(>98")then return item ordering# different worksheet datadase.
WALL SELECTIONColor NamePatternFinishExisting
Wall Ht.Wall System stops at:
Wall Repair[SELECT COLOR][SELECT PATTERN][FINISH][SELECT HEIGHT][SELECT HEIGHT][REPAIR]Plum
Wall SpecsSoap Dish Wall SpecsBack Wall SpecsExtra
Wall Specs (if needed)Finished Size:[WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT]
Quanity:[QTY][QTY][QTY][QTY]Item Ordering #:
View 2 Replies
View Related
Nov 7, 2006
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice:
Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc.
Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10
Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more
The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View 7 Replies
View Related
Nov 16, 2009
I have several rows with numbers that is either 0, a balance or a text like n/a. I need a formula that return true if all are 0 or contain n/a. If there is one balance shall it be false. Any suggestion? See example:
View 6 Replies
View Related
Sep 28, 2009
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing.
So, If PRODUCT_A with OPTION_A, then PRICE = $X.
If PRODUCT_A with OPTION_B, then PRICE = $Y.
If PRODUCT_B with OPTION_C, then PRICE = $Z.
etc.
I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
View 3 Replies
View Related
Aug 22, 2013
how to return values based on some conditions.
So for the sake of this example i have two cells lets call them J1 & J2 , one with the client name and another with State (matches the table name)
How can i get it so when i type in say Client 1 & Table 3 it will return the information in client 1s column into table 1s rate column. Obviously i want to be able to type in any clients name / table and have the same thing happen.
the red is what i want returned into table 1s rate column
Table 1
Table 1
Table 2
Table 2
[Code].....
View 1 Replies
View Related
Jul 26, 2014
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies
View Related
Oct 16, 2006
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)
Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses
Is there a formula/function that i can use (like the IF Formula) to complete the following information;
EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"
Column H lists all departments
Column A lists deaprtments
A3 representing the 1st Department needing training
View 8 Replies
View Related
Apr 23, 2008
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
View 9 Replies
View Related
Jun 1, 2008
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View 4 Replies
View Related
Oct 17, 2007
I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies
View Related
Jan 30, 2014
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm‎
View 14 Replies
View Related
Aug 18, 2014
How to return address of the column or cell I selected from Application.Inputbox, not just return the value?
[Code] ....
View 8 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
Oct 4, 2009
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
View 8 Replies
View Related
Jun 25, 2009
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies
View Related
Aug 14, 2006
I want to calculate the sum of column with two conditions on different
worksheets,
Example
Data
Stock #QTYMonth
01335208Aug-06
01337402Aug-06
01337392Aug-06
01335400Sep-06
01337100Sep-06
01337102Sep-06
If the stock # is equal to the stock # and Month equal to month
I need to get the result in another sheet as below.
Stock #Aug-06Sep-06
01335208400
01337794202
View 13 Replies
View Related
Mar 13, 2008
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
View 9 Replies
View Related
Mar 6, 2013
I have the following code:
=SUMPRODUCT(--($A$2:$A$400="OKC"),--($C$2:$C$400="AF33"),$G$2:$G$400)
It works great, but I am needing to now add a 3rd condition, or filter... In addition to it looking for "OKC" and "AF33" I also need it to sort by month. example; "March". I need it to be in code that works on old versions of excel as well...
View 6 Replies
View Related
Feb 15, 2008
Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting.
=SUMPRODUCT(IF(AND($K$22:$K$169="A&B",YEAR($G$22:$G$169)-YEAR('Info'!$F$9)=0),(MOD(ROW($R$22:$R$169),2)=0)*($R$22:$R$169),0))
where: K22:K169 are user entered categories from a drop down list
G22:G169 are user entered dates
F9 is a user entered update date
column R are monetary values with first row being a revenue$ second row margin$
I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
View 9 Replies
View Related
Apr 28, 2006
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors.
The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
View 6 Replies
View Related
Jun 25, 2006
I've been writing a piece of VBA code to process a timesheet for my office.
THe sub routine has a number of variables passed to it, two of which are called CFhrs and Limit. The first contains in integer representing a number of hours. THe latter is a number of hours that can be claimed. In short if CFhrs is less than Limit, the CFhrs should be left alone, but if CFhrs is more than limit then CFhrs should then equal Limit.
First I tried this:
If CFhrs = Limit Or CFhrs > Limit Then
CFhrs = Limit
CFmins = 0
End If
This seemed to be totally ignored when run, and even when CFhrs was greater than Limit, CFhrs was left just the same. So I tried:
CFhrs = IIf(CFhrs < Limit, CFhrs, Limit)
CFmins = IIf(CFhrs < Limit, CFmins, 0)
View 9 Replies
View Related
Jun 19, 2014
I am having trouble with formulas below, when i try to use AND, OR, i having trouble .
=IF(B1=SG,IF(A1="I",0,IF(A1="A",0,IF(A1="P",0,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=LQT,IF(A1="I",150,IF(A1="A",150,IF(A1="P",150,IF(A1="D",44,IF(A1="W",44,IF(A1="O",44,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=MLT,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=IBW,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",10+(C2*15%),IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=JL,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
View 8 Replies
View Related
Feb 15, 2014
I have a table(see attached). I want to sum the total hours worked in Col C. Col B has the number of people on the job (either 1 or 2). I need a condition, I guess , so that if Col b has a number 2 in it, then the adjacent cell needs to be multiplied by 2, then summed into the total hours worked.
I am not sure on how i would write a formula for this. Looking at the sample if I add the total hours from col C at the moment I get a total of 18 Hours but as cell C3 & C5 have a "2" in their adjacent column, the total hours answer should be 27 hours.
View 3 Replies
View Related
Mar 22, 2014
SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))
place the amount from table column "IN" into a corresponding cell in another sheet. how to add another column (OUT) in the above formula. I wish to summarize both column IN and OUT from table based on two conditions.
Workbook example: [URL]
View 3 Replies
View Related
Aug 13, 2007
I have a table (look at attached file for better info) with TEXT values on Column A (A1:A10) and NUMERICAL values on Column B (B1:B10).
I want to find the MAX value between a value that I set (in cell A14) and the last record (as well as the opposite, between the value I set and the first record).
What this means is that if I set for example D as my value (see attached file for better understanding) I want the formula to find the MAX value between B4:B10 (and another formula that finds the MAX value between B4:B1) based on these conditions
The first and last values (i.e B1,B10) are not included in the result.
The value I set is not included in the result as well.
What this means is that if I set the value D which can be located in cell A4, I want to find the MAX between cells B5:B9 (and with another similar formula the MAX value between cells B3:B1).
The value I set changes every time.
I have created a formula as you will see in the attached file but that works only if I create a copy column A to C and then again I can't find a way to exclude the value I set from my results, so if the value I am looking for happens to be the MAX I get that as a result.
View 14 Replies
View Related
Feb 2, 2009
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this.
I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
View 3 Replies
View Related
