Reset Cell Value Based On Two Conditions
I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula To Reset A Count Based On Cell Value
I've found a # of threads on this subject but have been unable to find the function I need. I've got a spreadsheet (attached) to calculate vacation time, but I can't figure out how to make the used time reset on an anniversay date. For example, if Employee 1 has earned 80 hours of vacation but only used 40 by his 4 year anniversary date, how do I make unused time roll back to 80 the day of his anniversary? Here's the structure: 2 Personal days given Jan. 1, then 2 more on July 1 to employees here at least 6 months. Those whose 6 months fall after or between are awarded 2 days on their anniversary then accrue as normal. Unused personal days are lost at the end of the year. Vacation time is 40 hours after 1 year, 80 hours at 3 yrs, then 120 hours at 5+.
View Replies!
View Related
Cell Value Based On Multiple Conditions
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10. I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View Replies!
View Related
Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem: Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it. i.e. (in reference to attachment - sheet DATA) IF E6 = Holiday, then D6 = Red IF E6 = Half Day Holiday, then D6 = Red IF E6 = Sick, then D6 = Blue IF E6 = Sick Half Day, then D6 = Blue IF E6 = Bank Holiday, then D6 = Green IF E6 = Compassionate Leave, then D6 = Pink IF E6 = Unpaid Leave then, D6 = Yellow IF E6 = Unpaid Leave Half Day then, D6 = Yellow However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
View Replies!
View Related
Return Formula Value/Text Based On Many Cell Conditions
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 Replies!
View Related
Cell Comments Line Breaks Based On Conditions
I manually enter data into inserted comments in a worksheet. Is it possible to run a macro that formats comments to align based on spaces? Ie Entering "City","State" and "Amount" on row 1 in a comment and entering "Denver", "Colorado" and "5" in the second row would produce those words to align at a predetermined spacing (ie after every space the next word would start 10 characters over) ----------------- City State Amount Denver Colorado 5
View Replies!
View Related
Change Cell Background Color Based On Multiple Conditions
My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status. OK : green FAIL: red N/A: orange Exceptions: blue The attached file shows a few possible results, manually entered.
View Replies!
View Related
Running Total To Reset Based On Criteria
Have a sample file attached, and what I want to do is as follows- Column H shows my current position, and column L shows the p&l. I would like excel to calculate the p&l for each position, and put the results in column M. For example, H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit, and I have no idea how to do it
View Replies!
View Related
Reset Dropdown Lists Based On Other Inputs / Cells
Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost. So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next. But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed. Let me try and simplify... 'Menu 1' has two option: 'A' and 'B'. If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'. If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'. Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'.... 'Menu 2' will still be displaying '1', even though the options are only '3' and '4'. Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
View Replies!
View Related
Reset Cell Value To Zero
i have cells A1 to A10 filled with numbers i have cells B1 to B10 blank what i need to do is when something is typed in cell B1 - A1 to A5 are reset to zero and when i type something in cell B2 - A2 to A6 reset to zero. I have tried to do this as a range ie A1- A10 have numbers in B1 - B10 copy these numbers and reset if anything is typed in C1 with this formula =IF(C1:C5="",A1,"0") what it should do is reset B1 to zero if anything is typed in C1-C5 but only when something is typed in C1 does this work ..
View Replies!
View Related
Reset Last Used Cell Without Saving
if there is a way to reset the last used cell without saving? I have macro first brings in about 30,000 rows of data, but deletes most of it before it finishes running. This works fine, until the third time I try to refresh the report, when Excel won't let me insert another 30,000 rows of data because it would push the last used cell off the bottom of the report and that's apparently not okay, even if the cell is completely blank. I have it set to automatically clear the blank rows and save when the report opens, but that doesn't help if they run it too often before that happens. I don't want to do a background save because they may have made changes that they don't want to save.
View Replies!
View Related
Two Cell Accumulator Then Reset First Cells
I basically have four cells c8 = goods in (new components in) c9 = kanban stock (current qty in stores) c10 = current stock (=kanban stock minus allocated) c11 = allocated (number on order) What I would like to be able to do is enter a figure in c8. This would then start the macro to update c9,10 and 11. The problem I have is that if i enter a figure into c8 sure it alters the others if i do a quick =sum formula but does not reset. Therefore if I put for example 6 in cell c8 and then want to add another 8 all other cells alter accordingly. I want all cells apart from c8 to update and hold the figure after c8 has been entered.
View Replies!
View Related
Reset Value In Dependent Cell When Another Cell Is Updated
1. I created a "Top Category Data Dependent Validation List for sub-categories” in the attached spreadsheet. The selection from the Category drop-down list in column B, then drives or limits the choices in the Sub-category drop-down list in column C. Credits: To achieve the above technique, I used the "Dependent List Validation" document as a technical reference. the document is available from http://www.ozgrid.com/Excel/dependent-lists.htm 2. If the user updates the Category selection in Column B, then the value in the corresponding row in Column C should be "reset to a ( NULL/Clear) value," as to automatically prevent any human-error in forgetting to also update the Sub-category data in Column C. (Optional Reading--the following steps are not related to steps 1 & 2 above, just notes regarding other data validation process created in this spreadsheet 3. I also created another column (D) that gets automatically populated with the Category ID, based on the sub-category drop-down list selection in C. 4. Also in this spreadsheet, a macro checks for any "missing required data before allowing the end-user to save this worksheet. As an example, In Column A, if there is a "RecordId" that exists in the same row, then the macro checks if the user has entered required values in Column B, C, "AND/OR" D. If the end-user did not enter any of the required values, then an error message pops-up with this message: "Cannot Save this file due to Missing Required Data. Please review highlighted record and complete missing data." Credits: Special thanks to: -OzGrid Business Applications for writing code for a UDF (function that returns last word) that I used/modified a little bit to achieve Step 3. -Carl (member name" carlmack") for his help in the methods I used in Step 4 above.
View Replies!
View Related
Lookup Based On Two Conditions ...
I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1. The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H. So I need to return something like this: ...
View Replies!
View Related
Get A Result Based On Several Different Conditions
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in. The following is a table which shows a salesmans sales. He or she has sold to 8 customers. What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria. 1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40 2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25 3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
View Replies!
View Related
Display Value Based On 2 Conditions
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course. The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
View Replies!
View Related
Lookup Based On A Few Conditions
I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View Replies!
View Related
Lookup Based On 2 Conditions
In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View Replies!
View Related
Sum Based On 2 Conditions
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime. Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View Replies!
View Related
Summing Based On Multiple Conditions
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example: 1 A 1 1 B 2 2 A 3 2 B 4 3 A 5 3 B 6 I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
View Replies!
View Related
Sum Based On Multiple Conditions
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.
View Replies!
View Related
Selecting Values Based On Conditions
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received. I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
View Replies!
View Related
Extract From List Based On Conditions
I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet For example, the following list would be imported from an inventory program as text and pasted on worksheet 1: P+green beans d-2.99 c=2 cd+10258 p+cherry tomatoes d-3.99 c=1 cd+11204 p+red apples d-.69/lb c=3 cd+08902 p+tissue d-1.99 c-2 cd+20023 How can I have Excel take this information and create a list like the following on worksheet 2? ...
View Replies!
View Related
Calculate An If Value Based On A Number Of Conditions
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input. For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input). the formula I started out with was: =IF((A6=1),"$8.00") this worked for me if A6 did in fact equal 1. So I tried adding this equation to the formula. =IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula. I got the error #VALUE!
View Replies!
View Related
SUMIF Based On 2 Conditions From 2 Columns
Have had a search around the web forums for an answer to this and am not getting anywhere. In column A i have text - apr, may, jun etc In column E i have general format text - a 6 figure number in column M i have text - eg 886.97 (a currency, but in text format) i want to: where column a=apr and column E=212130 sum the contents in column M
View Replies!
View Related
Returning A Text Value Based On 2+ Conditions
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes. A B C E F G M S Get value from G M S 5G M T M T 6M M U M U 7H M V M V 8K
View Replies!
View Related
Average Based On Multiple Conditions
Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:- =IF((NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)=0,"",(NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)) This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0)) This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.
View Replies!
View Related
Extract Certain Data Based On Conditions
method to extract certain data from a column of values. The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list. I have manually entered the info for the 1st June as an example.
View Replies!
View Related
Add Sheets Based On Conditions
i am not able to solve this problem. 1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table 2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month. 3- Reference table has the list of the same data record ( numbers ) . Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following. a- Check for the record in the reference table , if doesnt exist then add the record in the reference table b- create another sheet same as the sheet " A" (duplicate of A) and name it the new record number b- Plug the new record number in the cell B2 of the new sheet c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to O8)from sheet A
View Replies!
View Related
Return Data Based On Two Conditions
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 Replies!
View Related
Copy Range Based On Certain Conditions
I have this macro to copy a range to another worksheet: Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y ' las cajas ATR Dim rng1 As Range Dim rng2 As Range Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown)) Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown)) rng1.Copy Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True rng2.Copy Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Application.CutCopyMode = True End Sub The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.
View Replies!
View Related
Flagging Duplicates Based On Multiple Conditions
Flagging Duplicates based on Multiple conditions.. I have attached the file for reference with Dummy Data as the actual data runs in thousands... What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records... The Duplications has to be considered on Multiple criterions: 1. If the FNAme and LName is appearing for one Company more than once. 2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same. 3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate". Ex: - 4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique. Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique 5. If still the records are identical then the first occurence can be unique... I have made this data to give an example as the actual data is very confidential. Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic. Please refere the Word doc Duplication Criterion Explanation for the explanation.
View Replies!
View Related
Trying To Use SUMIFS To Sum A Column Based On Two Conditions
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving. Here is my formula that works so far. =SUMIF(Data!C:C,"Office",Data!D:D) Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format. 1/9/2010 9:21:49 AM I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS. =SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates") Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
View Replies!
View Related
Populate List Based On Two Selected Conditions
I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for. This worksheet posesses amongst others; two columns titled location and sublocation. I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions) This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.
View Replies!
View Related
Highlight Non Matching Rows Based On Conditions
I am having a problem finding or creating a macro for what i am needing to do. I have a workbook with 2 worksheets (2 months july and august) to figure out a billing process. I need to be able to First of all search each value in Column A on sheet 2 (august) to Column A on sheet1 (July). My sheet is set up like this where ID is a buyer and Products 1,2,3...are if they have purchased that product from us A B C D 1 ID Product_1 Product_2 Product_3 2 100 0 0 1 3 200 1 1 0 4 300 1 0 0 5 400 0 1 0 Better Picture representation attached Both worksheets are very similar but August may have some changes from July such as new ID's and changed prduct purchases. What I need to do is have each ID in Column A be compared to Column A in the July sheet, but it may not always be the same cell (because of new ID's added they are moved around)..so it need to search for the same ID first then compare. Once it finds the ID, I then need it to compare the rest of the row (the product info) for that ID to see if any products were added or changed to determine billing. And for any changes that are found in the comparison, I need them to be highlighted. Now in the case that a new ID was added...it wont be in the July sheet, so the whole row would need to be highlighted to notify of a new ID and new billing needs to be added. This way the billing person can look at the sheet, see the highlited changes and see what is new or needs to be added to a previous ID's billing. Currnelty they are looking at it manually to see if there are changes.
View Replies!
View Related
Hiding A Command Button Based On Conditions
I am using Excel 2003. I have created a workbook containing two sheets. The first sheet is designed as a form for our managers to complete. The fields they are required to complete are based on the selection they choose from a drop down field (set up through data validation). I have created a command button which when you click it opens up the second sheet of the workbook asking you to complete the individuals work pattern. I am trying to hide this button so that it only appears when you select certain options from the drop down field. I have looked back through posts on here and have tried adding the VBA code to the worksheet around commandbutton1.vissible = False in an IF statement but can't get this to work. A colleague has suggested that you can't hide command buttons because they are fixed items - is this the case.
View Replies!
View Related
Conditional Formatting More Than 3 Conditions Based On Dates
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it). My conditions are: 1. Turn the cell background colour red (action overdue) if: Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1] 2. Turn the cell background colour orange (action required) if: Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
View Replies!
View Related
Counts Info Based On Multiple Conditions
I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute column A = Home Team B=Away Team C= Time of First goal D=Time of Second goal E=Time of Third etc I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50. My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1. The sheet has a lot more columns but this is the main sticking point.
View Replies!
View Related
Summary Of Numerical Values, Based On Several Conditions
I am trying to do is a summary of numerical values, based on several conditions. column a column b column c local loop live cross connect loop A cancel 100 long haul live 10000 long haul pend canc 1000 loop Z live 100 x-conn live cross connect In column a, i have several choices and i want to focus on those choices that fall into column B "live" or "pend canc" and then only have numerical data in column C I've tried several different formulas like =SUMPRODUCT((a30:a38="Loop A")*(a30:a38="Long Haul")+(b30:b38="Live"),cX30:cX38) I know the result of this should be 111000 but it's not working...I usually get n/a. I know about using cntl-shift-enter to use the formula properly. I can get one variable to sum up properly using =SUMIF(M9:M21,"Live",BW9:BW21) but, that leaves out anything that is pend-canc I have also tried =SUM(IF((a9:a21="long haul")*(b9:b21="live"),cX9:cX21)) which gives back a value that is accurate.....
View Replies!
View Related
Adding Ever Extending Data Based On Conditions
I work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas. The data is imported into excel from a database and therefore is constantly growing. What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service. Because the data is ever expanding I would like this on a separate sheet from the actual data. I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
View Replies!
View Related
Lookup Unique Based On Multiple Conditions ...
I am looking for a solution other than using an advanced data filter for unique records only. I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B). Group A only includes records with value ="A" in the checksheet column. Group B includes records with value <>"A" in the checksheet column. Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated? SEE ATTACHED:
View Replies!
View Related
Concatenate Cells Or Ranges Based On Conditions
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter. "| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs ( | are cells) In another cell, i would like to merge them together like so "A01-A07" are for Joe Bloggs And also as follows "| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith would like this to be "A01, A03-A05,A07" are for John Smith. So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
|
TRACKER
