Sum Cells And Return Results
Jun 25, 2013I would like a formula to calculate the following:
If either one of U8 or AP8 are blank return blank (both need to have a number in)
If sum of U8 & AP8
ADVERTISEMENT
Single Formula/Function Return Results To Multiple Cells
Aug 26, 2006I've got an If statement in Excel which features several VLookups - I need the IF statement to return differing results to 2 different cells, is this possible?
In plain English, I'm looking for something like
If(Vlookup(A2, B2:D5, 4, False), C6="y" And D7="ok", "False")
Obviously this is a very crude example
Search Workbook For Cells That Meet Multiple Criteria And Return Results From Rows
Jun 28, 2013I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
Create_List.xlsx
Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?
Feb 1, 2012Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
Check A Value And Return Results
May 5, 2008I have a table that has 6 columns and 5 rows. I need to check a name (division) against the column heading, then and return a classification from the first column based on the score.
Below is an example of what I am using (I'm not sure how to post a screen shot):
DIV1 DIV2DIV3DIV4DIV5
A91.7689.4198.82100.82102.35
B111.43108.57120.00122.00124.29
C141.82138.18152.73154.73158.18
D195.00190.00210.00212.00217.50
E195.10190.10210.10212.10217.60
DivisionDIV3
Total Score 155.6
ClassificationC
Formula Return The Same Results
Nov 11, 2009The formula in Col G uses the value in col's B:E. I would like a formula in Col G that will use the 4-digit value in Col F ( same values as Col B:E) and return the same results.
******** ******************** ************************************************************************>Microsoft Excel - PLAY 4 EVE MOSTLY.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1F2G2F3G3F4G4F5G5F6G6=ABCDEFG111/10/0966666666Q211/09/0977667766DD311/10/0977767776T411/11/0972467246S511/12/0977667766DD611/13/0978667866DSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
If Function To Return One Of 3 Results
Nov 22, 2007I am looking for a formula that will yield the following: IFAH9>AI9 return the word Green, IFAH9=AI9 return the word Blue, IFAH9<AI9 return the word Yellow. If AI9 is blank stay blank.
View 3 Replies View RelatedLookup And Return Multiple Results
Mar 31, 2014I want to look up a particular value and return according results horizontally and vertically. Attached is the excel.
View 10 Replies View RelatedLookup Value And Return Multiple Results
May 4, 2014I have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
Search Tabs And Return Results
Feb 2, 2010I would like to summarize all "yes" respose contained in 15 tabs. See the attached.
I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.
COUNTIF - No Results Return Only Formula
May 23, 2014I am trying to track the number of "F"s within a cell and I'm sure that I'm using the formula correctly -
=COUNTIF(I2:I29,"F").
When I use the insert function feature to walk through setting up the function step-by-step - I see the count once I type in the criteria - however, when I hit 'Ok' the count doesn't appear in the cell - the formula appears. I have cut and pasted the entire worksheet into a new spreadsheet, the problem remains.
Search A Column & Return Many Results
Jul 8, 2008I have a workbook with two worksheets.
On Sheet1 I have two columns 'Date' & 'Serial Number'
I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
Return Lookup Results Of All Occurences
Jan 30, 2008I am using vlookup to return data from a range that could have the same name in column A.
There are 8 columns
Column 1 is Name
Column 2 is Sun
Column 3 is Mon
and so on.
The problem I have is that a name can appear in two places in column1 with relevant information in different columns:
Fred appears twice, on Wed he is on a course the rest of the week he is on leave. I need a C to be returned for Wed & A/l for the rest of the week.
Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing
Jun 25, 2009Hi, 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 RelatedLookup Formula To Return Multiple Results?
Dec 8, 2013I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.
Auto Filter Very Slow To Return Results
Feb 12, 2009i have a work book of 12 sheets, the two main sheets 1+2 have 5000+ rows. by 30 columns. each row column b is a site number in ascending order(up to 4 for each site) so auto filter select = to site 5 about 10 seconds later i get the result of 3 sites try site 846 30 odd seconds later up pops the result. so i copied the sheet to a new work book and tried and it's nigh on instantaneous. next step
copied sheet paste special values into new sheet in same original workbook
and tried filter on that still slow as ever, thing is this book around 8meg and i cant for the life of me work out why ive cleared excessive formats and reset last cell on each sheet. there is a vlookup from sheet 1 to 2 for each site to return serial numbers. i have tried it in vba by turning autocal off/on but still no difference.
Conditional Formatting To Return The Following Results If The Criteria Is Met
Jun 19, 2009I 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
MODE() Is There A Way To Return Bi-modal Or Multimodal Results
Jan 8, 2006I'm working with lists of numbers that should return bi and multi-modal
results when analyzed.
Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?
Can Index Or Match Return Multiple Results
Nov 27, 2006I've spent the last week trying to do the following, with little to no success....and I'm running out of hair to pull out!!
I have a worksheet (let's call it DATA) with the following column headers:
* Site Name
* Year Initiated
* Status
plus a whole bunch of other columns of information.
On a separate worksheet (let's call it RESULTS) I'm trying to create a summary of all the Site Names where the Year Initiated = "2004" and the Status = "Active". There will be multiple occurences where this is true.
I then want to create further results where the year = 2005, 2006, etc., etc. I've been trying to use the INDEX function with variations of MATCH, SMALL, ROW, etc., etc., but so far, no luck.
Advanced Filtering is not an option as I need to do further manipulating of the results on the RESULTS worksheet.
Return Text Results Based On 2 Criterion
Oct 6, 2008I am attempting to create a formula that will, return text results based on 2 criterion.
This is what my sheet looks like
Name Job Full Time / Partime
Bob Admin Full Time
Barry Accounts Part Time
Chris Claims Full Time
Emma Claims Full Time
The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))}
Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something.
I would use a pivot table to do this, however I need the sheet to be completely automated.
How To Return The Maximum Value In A Multiple Results LOOKUP
Dec 11, 2008What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
Return Value Based On Results Of Conditional Formatting
Apr 19, 2012I have a report that shows when our company has shipments covered by inventory (although you are not seeing the conditional formatting in the table). Columns K:Q represent shipment dates and shipment amounts. Columns D:I represent inventory with Column J being the total inventory. The conditional formatting works to highlight the cells in K:O of when shipments are covered by inventory. I need to be able to return the date (Columns K:Q) for an item (Column B) when a shipment is not covered by inventory.
For example Row 154: all shipments are covered by the amount of inventory. A shipment of 3 on 04/24 is more than covered the 105 pieces of inventory. But for Row 170, the shipment for 480 pieces on 04/23/12 does not have enough inventory to cover.
The result I need to return for row 170 is that 480 pieces are due on 04/23/12 that are not covered.
What formula would I used to return that value?
Excel 2010ABCDEFGHIJKLMNOPQ1LineITEMNEXT CUSTOMER PART WIP INVENTORYSTAGINGFINISHEDINSPECTDCYTRAILERTOTALPast4/18/20124/19/20124/20/20124/23/20124/24/20124/25/2012154Tabber105290325AA05290325AA-DC000010501053155Tabber1070025AC-EXP070025AC-
[Code]....
Return Results Of Ping Test To Worksheet
Oct 18, 2012I'm trying to work out some code that will take the contents of a cell (either alias or IP name), carry out a ping operation and return the result into the worksheet in the next columns to the right.
I've searched this site and found something similar that returns the result of the ping to a MSGBOX but this isn't quite what I want.
The endgame is to have a For/Next loop for a list of IP addresses in (say) A1:A10 and have the results pasted into columns B, C and so on, with each line of the ping result pasted into subsequent columns.
Formula To Return Number Of Results From Table
Mar 12, 2013I have the following table in a spreadsheet and want to return the number of rows where the Matched column is "F" and the Type column is "L".
Ref
Selection
Stock
Order
Matched
Type
[code].....
So in the example given the formula should return 1.
Formula To Return Results Based On 2 Parameters?
Jul 5, 20131) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
Serach A Column And Return Results Using Macro
Mar 13, 2007Im trying to write a marco that will look in colum E and find criteria "a", then offset (2,1) and copy the activecell and the one below it to a1:a2, then return to where it was and offset (0,1) and then copy the active cell and the one below it to a4:a5.
after this is accomplished i need it to return to column E and start the process again for "critera b" paste the result to a7:a8 and a10:a11
I know folks usally post examples of the there code for other to take a peek at however I only know bits and pieces of the process and im not sure how to tie it all together...I was hoping someone could help me get a good start....thanks
Return Results Based On Referenced Cell
Nov 21, 2006I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)
View 3 Replies View RelatedReturn Multiple Results Based On Criteria
Dec 1, 2006Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
Lookup All Occurrences & Return Results To 1 Cell
Jul 13, 2007My task is that I have several documents exported in excel. The requirements in them were mapped to another document. The IDs were populated in a certain column but are now no longer needed. I have to convert those old Ids to the current ones. In my example I have populated Sheet one with the lookup table. In column A are the old IDs. In Column B are the new ones they map to. The old ones can map to several which is why there are more than one ID in some of the cells. In column D of Sheet 2 I have populated it with the old Ids. In Column A I'd like to have the lookup return the appropriate maps to the new Ids. For example for the first map in cell A2 it should return:
abc_18
abc_43
abc_6
abc_4
all in one cell.
Vlookup can return the first hit (abc_18 and abc_43 for cell D2), but ignores the other two IDs in cell D2. I was able to work around this combining the find, mid, and vlookup to return all values. The problem with that is that the formula gets monstrously big after just having it return 4. In the actual files it can be up to 30 IDs which would make that formula literally pages long since. I have pretty much no experience with macros, but it seems like the only solution since I have to send this off to various people so they can use it.
Get AND Function To Return Back Results Other Than True And False
Mar 5, 2014Here's my formula:
=AND([@[Tenure (Yrs)]]>=5,[@[2016 Sales]]>=150000)
(Where should I enter high and low? Do I need an if formula nested into it?)
I'm trying to get it to read if the customer reads both criteria which is they've been there 5 years and over 150000 sales =
High if not Low.