Array Formula: Add Data Which Meets Certain Conditions
I'm working with wookbooks used company wide and I cannot add any helper columns which would solve the problem. I need to add data which meets certain conditions see attached workbook for a sample.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Summing Data That Meets Two Conditions
I want to sum certain data, which meets two conditions. My data set contains three columns and a lot of rows. The columns are the following: PostalCodeDeparture PostalCodeArrival PassengersInCar I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set) With "SUMIF" I can only include one condition.
View Replies!
View Related
Countif (check The Data For The Following Conditions, If It Meets The Crirteria Then Place A 1 In Columns)
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked. Column: A B C D E Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result) What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E. Criteria 1 If any of the coulmn data contains a value less than .5 I need a 1 placed in column D Criteria 2 If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
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
Add Nested Array Formula To Range
I have the following code that I am trying to distribute over an array. The best case scenario is that the code enters the text of the formula instead of the calculated value. When I tried to tweak a few things I couldn't even get the text to show up. This seems to only happen on the formulas which include If_Then statements. Other straight forward formulas such as "sum" etc... seem to go into place and calculate. Sheets(1). Range("b3:s3").FormulaArrayR1C1 = _ "=IF(AND(MAX(2!R[2]C:R[6]C)>0,SUM(3!R[1]C:R[5]C)<0),MAX(2!R[2]C:R[6]C),"")"
View Replies!
View Related
Add Lookup Value In Array Formula To Each Worksheet With VBA
I am looking to write a macro that can look at a value (i.e. "X") in a cell and compare it to the elements in a 2D array (where one dimension corresponds to the start value and the other to the end value) that is found on another worksheet. If "X" falls between the starting and ending numbers then I would like the macro to return a string (say "Test1") to a cell on the same row and a couple of columns to the left. The string values are listed to the left of the start and end values on the first worksheet and are elements to a 1D array. I am trying to make this " loop" through all the cells that contain run numbers (I have highlighted the columns in yellow on the attached workbook). There are multiple (i.e. +12) worksheets in this project but I have only included two in the sample I am providing because of file size.
View Replies!
View Related
Sumif Formula The Meets Five Different Criteria
I want to create a sumif formula that will sum the data if it meets five different criteria. I tied to do an “Or” statement in the formula, but it doesn’t work. For example, I want to sum all the rows that contain: Apples, Bananas, Cherries, Pears, and Plums. How do I write the sumif formula so that it will do this?
View Replies!
View Related
SUMIF Sum All Data That Meets 1 Or 2
Suppose I have some data with a code for each data point: 1 100 1 200 1 300 2 400 2 500 3 600 The first column is the code and the second is the data. I can use a SUMIF statement to sum all the data that have a certain code (like 1). What if I wanted to sum all data that meets one of a number of codes? Suppose I wanted to sum all data that meets 1 or 2. I know I can do this with 2 separate SUMIFs, but I was wondering if there was a way to do it with one.
View Replies!
View Related
How To Make A Macro Take Action When Data Meets Certain Criteria
I am watching 100 stocks when the stock market opens at 9:30 EST. Not all the stocks will come available to buy or sell at 9:30 but will become available at different time intervals, sometimes 10 minutes after the market opens. When a stock opens it is common for it to spike up, then spike down, then go into a "normal" trading pattern, this is called a slingshot pattern. If I have a predetermined price up or down for 100 stocks, how can I write a macro that will look at the stock prices and if it shoots above or below a certain value it will submit a buy or sell order? (I already know how to submit the buy or sell orders, just need to get an idea of how to get the macro to constantly check the prices and if it meets my criteria to take action.) Note: I already have a macro running at one minute intervals to collect data. One minute intervals is to long, I need it in second intervals or less to pick up the slingshot pattern. Is this possible?
View Replies!
View Related
Data Binning And Array Formula
see attachment. I would like to calculate the average of the values whose time values fall in between the lower and upper time limits. Or let me rephrase that: If a given amount of time vector entries falls in between the lower limit time vector (at row x) and the upper limit time vector, then I would like to calculate the average of the corresponding value entries. (oh, not only the average but also the standard deviation) Searching the forum I found a similar entry, but unfortunately Andy Pope's solution attachement seems to be missing: [Solved] Formulas: Counting within bin sizes. I have tried to use the same approach (see attachment) but only came up with counting the frequency instead of calculating the average.
View Replies!
View Related
Array Formula: How To Aggegrate Data, Removing Blank Cells
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount. ie: ------------ column1 ------------ (blank) 1/1/2007 (blank) (blank) (blank) 2/5/2007 (blank) (blank) 3/7/2007 (blank) ... etc ... I would like some type of array formula, that could agegrate that whole column into a new column, removing the blank cells. so the new column would be: -------- Column2 -------- 1/1/2007 2/5/2007 3/7/2007 I was thinking something like... but this doesnt get rid of the blanks... (this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel. =ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))
View Replies!
View Related
Summing Values From Array W/ Multiple Conditions-cols
I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP. I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them. Example C F G H CUSTOMER JULY AUGUST SEPTEMBER customer1 2 3 4 customer1 2 3 4 customer2 2 3 4 customer2 2 3 4 The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H). The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would: sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName
View Replies!
View Related
Linking Partial Data From One Cell If Data In Another Cell Meets Requirements
The merged Cell B6:G6 will receive a ten-digit number followed by a dash and then one or more numbers. (For example: 1234567890-123) Cell B15 will then receive data shortly afterwards. I already have a validation macro for this cell which allows either 'I' or 'I I I'. Upon exiting Cell B15, merged Cell B16:H16 needs a macro which will check Cell B15 and if it contains 'I', Cell B16:H16 will display the data from the ten-digit number entered in Cell B6:G6 minus the first five digits. (For example: 67890-123) Now the data in Cell B16:H16 can only be somewhat editable hereafter. It can be erased or replaced with numbers in smaller or greater digit combinations than five before the dash (i.e. 67890-123 can be replaced with 123456-7), and digits can be added after the whole group (i.e. 67890-123 & SEE DWG) without any error messages. But if any five-digit number with a dash and some numbers exist in Cell B16:H16, they must correspond with the number in Cell B6:G6 minus the first five digits. However, if Cell B15 ever receives a 'I I I' afterwards, all data in Cell B16:H16 must be erased. Cell B16:H16 can never contain data if Cell B15 contains 'I I I'. Also, if the data in Cell B6:G6 changes later on, the corresponding digits in Cell B16:H16 must change as well, even if there are digits after the whole group. So here is an example of what a good macro would do for me: ...
View Replies!
View Related
Converting 3x10 Array To A 1X30 Array To Run A Match Formula
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1. Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows? Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet. And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column. I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
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
SumIf Formula: Add New Data With An Insert At Row 13
The formula that works is =SUM(IF('Pipeline Input'!$X$13:$X$39=1,IF('Pipeline Input'!$H$13:$H$39="Lead",'Pipeline Input'!$K$13:$K$39,0),0)) I am trying to modify this formula so that the ranges are dynamic to allow me to add new data with an insert at row 13. What would the syntax of the formula look like if I use the INDEX function to allow the ranges to grow with new data? I have tried naming the defined ranges and entering the formula as =SUM(IF((CloseMo)="1",IF((SaleP)="Lead",(LoanAmt),0),0)) but I get a #VALUE! error
View Replies!
View Related
Add Formula Dynamically Up To The End Of Data Entered
Below is the code. It seems to be creating 50 thousand rows below the data already entered in the worksheet "DELPHI DATA". What I need to do is change it to only add those formulae or pasted values to as many rows as already have data entered in them (which may eventually approach 50000, but may remain at only a few thousand.) Sub Refresh_Current_Month() ...
View Replies!
View Related
How To Add Range From Multiple Sheets With Conditions
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter. =SUM((start:finish!A2:A25="SU")*(start:finish!B2:B25="004*04")) gives me a #NAME? error. Example Data on each spreadsheet: A B SU 004*02 SU 004*04 SU 004*04 SU 004*02 SU 004*04
View Replies!
View Related
Convert Imported Dates & Add Periods Based On Conditions
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd I desire to look in column "E" and I have 3 different variables. "J", "K", "L". If column "E" has a j add 30 to the years; K add 35 years and L add 38 years. for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
View Replies!
View Related
Looking Up Row Array Data In Column Array
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8 What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31. In other words I want =sumif(A84:A89;"x";'X-ref'!T4:Y4) but it doesn't work since the first range is an column-array and the second range is an row-array.
View Replies!
View Related
Using Array Variable Instead Of Array Formula
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example
View Replies!
View Related
Add Unique Values Into An Array
I have a list of data in a range on an Excel worksheet. I want to create a macro that searches the range e.g. $A$1:$A$10 and identifies the unique cell values into an array. So if range data = "A,B,B,C,C,D,D,D,E" I would end up with an array containing 5 items "A,B,C,D,E". I would then like to cycle through each array item and bring up a message box displaying the value of each array item.
View Replies!
View Related
SUMIF (add The Values Of An Array)
I've been manually writing IF statements out for ranges of data that could easily be done with a little array work. So I set out to convert all my functions into something more readable and quicker to write. But I ran into a problem. I want to add the values of an array G45:Z45 if the corresponding values in G44:Z45 are less than or equal to P41. So I thought to use a SUMIF: =SUMIF(G44:Z45,<=+P41,G45:Z45) That didn't work, in fact, it didn't come error free until I did: =SUMIF(G44:Z45,"<="+P41,G45:Z45) But that doesn't add anything up either. From what I can see, the problem lies within the condition. If I simply put P41, it works. The moment I add <= I get a multitude of problems.
View Replies!
View Related
Add Array Formulas To Mutiple Ranges
I would like to set up VBA codes to generate a variable number of transition matrices. I would like to know how I can define dynamic ranges instead of coding each and every range. In the codes below, I have to define each range one by one instead of using a loop. Sub TransitionMatrix() Dim P1 As Range Set P1 = Cells(2, 2).Resize(3, 3) Dim P2 As Range Set P2 = P1.Offset(5, 0).Resize(3, 3) P2.Cells(0, 1) = "P2" For i = 1 To 3 For j = 1 To 3 P2.FormulaArray = "=MMULT(" & P1.Address & "," & P1.Address & ")" P2.BorderAround Weight:=xlMedium Next j Next i Dim P3 As Range Set P3 = P2.Offset(5, 0).Resize(3, 3)........................
View Replies!
View Related
IF Formula For Multiple Conditions
I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement: = IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER")))))))) But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula
View Replies!
View Related
Sum Formula With Conditions
I'm having toruble developing a formula to allow me to add all the values in cells A1 through A100 if their respective quantities are more than 0. What i mean is if i have the following columns of data: A B 6 2 2 1 4 0 A being the values added and B being the quantity then i only want A1 and A2 Added together. Therefore the value would be 8.
View Replies!
View Related
Formula To Select Value That Satisfies Conditions
Need a formula that will use column F as a reference to find out if there are 3 previous wins and show the value of FG (H column) and if not it should return just a character ("-" eg). For example in our case the end result should be what is shown in column J. The problem I have is that the sheet contains a break in stats after every 20 games and more conditions have to be added to accomodate this and have not been able to succesfully do it. You can use other columns (as shown) for references if it is easier. See column L for what I have come up with but it does not quite work as I wanted it - as it shows a false where it should return just "-" and also in the row after the last data entry it shows a 0 instead of "-". Table gets updated. Any other solution (not using IF function) would be fine as long as I get the end result in column J (as shown).
View Replies!
View Related
Combining Conditions Within Conditional Formula
i am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need. Basically i need to say the following: =if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")
View Replies!
View Related
SUMPRODUCT-formula With Changing Conditions
I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions. Like this one, which counts rows where conditions are met in four columns: =SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5)) and this one, which sums the values of the same =SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432)) How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions? Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row). I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs.
View Replies!
View Related
Conditional Formatting - With Two Conditions Within One Formula
I am trying to use conditional formatting for a cell; It is a cell using Validation; you can choose "Yes" or "No" from a list If the value is "Yes" the cell should be green, regardless of everything else But if the value is "No" it should turn red, but only if a choice in another cell is equal to "X". Otherwise it should be left without special formatting.
View Replies!
View Related
Internal Range Conditions Formula
Please take a look at this sample spreadsheet. [url] I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
View Replies!
View Related
Write A Nested If Formula With Special Conditions
I thought that a nested if is what I needed, but now I don't know what I need I have explained in greater detail within my sample that's attached. Quote:.... I don't know how to write a function for what I need. Right now I have a function that says '= if $b7=0,0,if($d7>$d5,-1,1)), but I've found a problem. If there's more than one zero it throws off what I'm doing. I need a function that will ignore any zeros within column D and use the second to last number. For example: The formula in E12 should read '=if($B12=0,0,if($D12>$D8,-1,1)) The formula in E13 should read '=if($b13=0,0,if($D13>$D9,-1,1)) The formula in E20 should read '=if($b20=0,0 ,if($d20>$d12,-1,1)) I just don't know how to write this so that it will go back to the second to last number other than zero.
View Replies!
View Related
Can A Macro Write A Formula As An Array Formula
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
View Replies!
View Related
Formula That Will Test Text Conditions In A Single Cell
I need a function that will use a column of text values and test these values to see if one or more of the values exist in a single cell. If it does I need the function to return true or false. Ie. cell A1 contains the text "Jim Smith" the B column contains the test names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim". Because Jim is in the cell A1 I would need the function in C1 to return the value "true". If A1 contained the text "bob smith" then function in C1 would return the value "false".
View Replies!
View Related
Formula To Locate Second Occurrence With Multiple Conditions Met
The purpose of the formula is to locate the 2nd occurrence of an entry where criteria in other columns are met (used to determine whether people can qualify for an item based on attendance and another condition). I am using this formula but I know it needs some adjustments and can't seem to work it out or maybe am going about it completely wrong. =IF(COUNTIF(D2:D205,D2)>1,AND(T2="Y",U2="Y")*1,0) I need the formula to indicate the second occurrence of this cell with the criteria that BOTH first & second occurrences have T2="Y" and U2="Y". My formula only indicates the first occurrence and the first occurrence if T2 and U2 meet the conditions.
View Replies!
View Related
Mega Formula To Calculate Results Based On Several Conditions
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking. For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05) The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
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
|
TRACKER
