Sum Range Of Data From Array Of Conditions
Mar 15, 2014
I am trying to pick certain data from a table and sum it in order to produce a formatted report.
The data has many different items of which I want to pick certain ones to sum on one line
See attached and some of the formula's that I have used, but aren't quite what I'm after
The formula I want to use is .... VLOOKUP(E5:G5,A14:C23,3,) ......
But it wont reference a range (E5:G5) !!! and I also want the formula to ignore blanks (as blanks in the data file are actual totals).
View 2 Replies
ADVERTISEMENT
May 14, 2014
1) Input data are static and helper columns can be added if needed.
2) Filter will be dynamic range (in attachement is the filter static), and the count of years can be changed on users request. So there could be only 2011, but also 2011+2012, 2012+2013+2014 etc.
3) In col 'J', the is what I know to do, but I do not want to use SUMIF+SUMIF+SUMIF... for each year (the count of years will change througt time as mentioned above).
View 3 Replies
View Related
Jan 1, 2009
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 5 Replies
View Related
Jul 31, 2008
My data is as follows
MonthMilesAmountLocation
June 8£3.20 Loc1
June 8£3.20 Loc1
June 8£3.20 Loc2
July 8£3.20 Loc2
July 8£3.20 Loc1
July 38£15.20Loc1
July 4£1.60 Loc2
Aug 38£15.20Loc2
Aug 4£1.60 Loc1
Aug 8£3.20 Loc1
Here is what I want to do
- Avoide filtering and manually calculating data for the total expense in the month
I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for
(4)LOCATION LOC1
I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???
View 10 Replies
View Related
Apr 23, 2009
refer to attached worksheet.
I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.
View 9 Replies
View Related
Oct 19, 2011
Given an input array (result of a calculation )
Input Array = [3 4 9 1 0 5 7 2 0 6 10 8 ]
Depending on the number and location of the zeroes, I would like to create a sorted array whose
- number of rows is determined by the number of zeroes in the input matrix (i.e number of zeroes - 1)
- number of columns determined by the the size of one maximum number of elements in one of the smaller arrays. The remaining entries on the other shorter rows will be made zero.
For the above example sorted array becomes a 3X4 array
1 3 4 9
2 5 7 0
6 8 10 0
Example 2
Input Array = [9 4 5 0 1 7 6 2 10 3 8]
Sorted Array is a 2X7 Array
4 5 9
1 2 3 6 7 8 10
I would like to generate this array in VBA (rather than writing/reading in a worksheet) as it will save a lot of calculation time.
View 3 Replies
View Related
Jan 16, 2010
I have a spreadsheet that contains two worksheets called: MasterApplication and LocalAdmin. The Local Admin worksheet as shown below contains the following information starting on row A1:D456 ....
View 9 Replies
View Related
May 11, 2011
I need a code to select sheets with pages less than 15 to print out. Also, a code to print out just the first 5 and last 5 pages if its more than 15 pages.
I've tried this so far but no success:
For X = 1 To Worksheets.Count
If Y Is Nothing Then
Set Y = Sheets(X)
Else
If ExecuteExcel4Macro("Get.Document(50)") < 10 Then
Set Y = Union(Y, Sheets(X))
End If
End If
Next X
Y.Select
View 6 Replies
View Related
Jul 18, 2014
How to apply a filter over the array "a" shown below and get the result in a new array "b" containing the filtered values based on the following conditions (the conditions criteria could be 1, 2 or 3. In this case only 2):
Criteria1 in Column 4="yellow"
Criteria2 in Column 3="ggg"
And only show values of columns 1 and 3.
If were using an SQL query would be something like this:
Code:
SELECT F[1],F[3] FROM "Table" WHERE F[4]="yellow" and F[3]="ggg"
The output array would be as below:
Code:
b=[{12,"ggg";140,ggg}]
this is the array:
Code:
Sub test()
a = [{"122","53","ggg","yellow";"140","9","ggg","yellow";"16","-22","ddc","yellow";"127","-37","ddc","green";"53","-28","ggg","grey"}]
'Filter code to get array b
'
'
End Sub
View 1 Replies
View Related
Sep 10, 2012
I had this code and it works like a charm, except, it crashed if the data gathered is just one element due to transpose ..
VB:
Function getAR(c1 As Range, c2 As Range, w As String) As Variant
Dim s As String
Dim arrTemp() As Variant
Dim arr() As Variant
[Code]......
View 5 Replies
View Related
Jan 14, 2009
I would like to have a formula in one cell that finds records on another sheet that meet certain criteria, and produces a sum of the total quantities associated with that record. The attached workbook has more details as to what I am trying to do.
View 2 Replies
View Related
Oct 1, 2007
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 9 Replies
View Related
Nov 9, 2007
My formula below work perfectly. I was wondering if their is a way for me to idenify the location of each row in which all the conditions are true. So, for example, if the formula generates an answer of 2, in a different cell it would give me the address of the 2 rows.
=SUM((K9:K72/L93))-SUM((K9:K72/L93))
View 9 Replies
View Related
Apr 28, 2014
I need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
=SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))
View 4 Replies
View Related
Oct 27, 2013
I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).
I'd like to use MyRange as the source for a Data Validation list in a cell.
Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).
When I set MyRange as the source for data validation however, it resolves to an error.
why this setup doesn't work?
Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx
View 3 Replies
View Related
Jul 25, 2012
I want to know how to copy a range into an array then an array back into an range.
Code:
Dim a(3,3) As Double
a(3,3) = Range("C3:E5")
Range("C10:E12") = a(3,3)
View 1 Replies
View Related
Aug 17, 2009
I have random "X"s in a column of cells in a range called "Won". I wish to count these if the adjacent cell in another range alled "Valu" is greater than a value determined in the cell E3. The result to be entered in Cell G4. Can use Excel formulas or VBA.
View 4 Replies
View Related
May 15, 2012
How to get the MAX of a range if 3 conditions are met.
1 after this date
2 before this date
3 Column G is equal to A2
Ranges are on sheet1
Date reference is on sheet2
range to sum is on sheet1
V14.
something like
MAX sheet1!H:H
IF sheet1!$G:$G equals $A2
AND sheet1!$P:$P,">="&EOMONTH($B2,-1)+1
AND sheet1!$P:$P,"
View 7 Replies
View Related
Aug 16, 2009
I have a named range “Value” and wish to count all the values over the value set in cell E3. The result to be entered in cell F4.
I have a second named range “Won”, adjacent to “Value”, and wish to count these values if the adjacent cell in the “Value” range has been counted. The result to be entered in cell G4.
View 2 Replies
View Related
May 15, 2012
How to get the sum of a range if 3 conditions are met.
1 after this date
2 before this date
3 Column G is "OTH"
Ranges are on sheet1
Date reference is on sheet2
range to sum is on sheet1
V14.
Present formula is:
=SUMIF('sheet1'!$P:$P,">="&EOMONTH($A2, -1)+1,'sheet1'!$H:$H)
-SUMIF('sheet1'!$P:$P,">"&EOMONTH($A2,0),'sheet1'!$H:$H)
This works fine but lacks the 3rd condition 'sheet1'!$G:$G ="OTH"
Advise given was to go on SUMPRODUCT but unless with (OFFSET(INDIRECT, SUMPRODUCT wouldn't work on multiple sheets. And this is for version 14...
View 5 Replies
View Related
Aug 10, 2009
I need a macro that will delete a range with some certain conditions. I have a set of dates in column B. The dates are in the format of 2009-01-31 09:15:00.
I need to search this column to find all the dates from the day before the computers date to the first day of the month before the previous days date. For example my computer date right now is August 10, 2009. So i need the macro to find all the dates from August 09, 2009 back to July 01, 2009 and select the entire rows that meet this criteria. Then I need to delete those rows.
Tomorrow when I run the macro it would choose all the data from July 01 - Aug 10, and this will go on each day until the end of december.
View 9 Replies
View Related
Dec 6, 2007
I have written a function below which is supposed to give average of selected range and will ignore #N/A, if any. Naturally, i use array formula:
Function m_avg(mRange As Range)
m_avg.FormulaArray = "=AVERAGE(IF(ISNUMBER(mRange),mRange))"
End Function
the above doesnt work and gives me #VALUE!.
View 3 Replies
View Related
Jul 27, 2012
I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.
First condition:
1 2 3 4 5 6 7 8 9 10
The function would return: 1+2+3+4+5 = 15
Second condition:
0 0 0 4 5 6 7 8 9 10
Function would return: 4 + 5 + 6 + 7 + 8 = 30
Third condition:
0 0 0 0 0 0 0 8 9 10
The function would return: 10 + 9 + 8 = 27
I have had success with:
=B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5)))
(using ctrl shift enter)
BUT it crashes when faced with the third condition.
I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.
View 3 Replies
View Related
Dec 17, 2012
I'm trying to code a clean up of some data ranges ready for priting / reporting. I need to move filtered data from the source sheet, to a new one.
The issue is, that I need to apply some logic to the rows I filter out, not something I can do with autofilter...
- Only copy rows including and BETWEEN the first and last rows where total count in col 8 is > 0
So in example1, I'd want to copy out rows P6 - 1A
BUT
- Always copy at least 5 rows when there is one value > 0 in col 8
So in example2, I'd want to copy out rows P5 - 1C, ideally...
Total
Boys
Girls
Total
[Code]....
View 3 Replies
View Related
Nov 13, 2006
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 9 Replies
View Related
Dec 7, 2006
In the screen shot I'm trying to find the row number where a particular price of an order has been reached. In this case, for the first order, my execution price is 1.8859, my stop loss is 1.8834 and take profit is 1.8884. I need to look and the future prices to determine which event had occured first (either the take profit or the stop loss). I though by using row numbers I would compare and which ever is smallest would mean that it occured first - the profit/loss is then calculated.
The other caveat is that an exact match may not always be available - for example, the second trade is stoped out because the highest price for the 12:35 timeframe exceeds the value I'm looking for. Still it would have triggered a stop loss.
******** ******************** ************************************************************************>Microsoft Excel - Misc.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6I6J6M6H7I7J7M7H8I8J8M8I9J9M9I10J10M10I11J11M11I12J12M12=
ABCDEFGHIJKLM3DateTimeOpenHighLow*Order*PlacedOrder*PriceStop*LossTake*ProfitStop*Loss*Row*#Take*Profit*Row*#Profit/Loss42006.11.1512:001.88651.88661.8863*N***these*are*the*cells*that*need*the*formula*52006.11.1512:051.88651.88661.8856*N******62006.11.1512:101.88591.88591.8857*Buy1.88591.88341.88841080.002572006.11.1512:151.88581.88591.8853*N*...............
View 10 Replies
View Related
Sep 5, 2006
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 9 Replies
View Related
May 16, 2007
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 3 Replies
View Related
Dec 7, 2007
I have a VERY complex custom data validation formula that is getting crazy. I thought it might be easier to use a user defined VBA function to handle it, but was unsuccessful.
I was able to write a VBA User Defined Function & use it within my spreadsheet to derive the value of another cell, as follows: =IF(OR(ISBLANK(B12),myValidation(B12)),"OK","NG")
I tried using it as a Custom Data Validation on the cell itself (=myValidation(B12)), and I get an error: "a named range you specified cannot be found".
Can the function be used in this way or could someone suggest another way of handling this?
View 6 Replies
View Related
Mar 15, 2013
Assume this Data in a spreadsheet
I want to count blank "PI" based on "Product name" & "Product date" occurence together
So I created intermediate field "IS Blank", and I dunno what formula can give me the below results
My obective to get this result
CountBlank for PI = 3
--------------------------------------------------------------------------------
Product Name..... Product Date.... PI...... IsBlank "PI" [Desired Formula output]
xxx .......................ddd ...............Blank.................. 1
xxx .......................ddd ...............Blank.................. 0 (counted above for same xxx&ddd)
xxx .......................ddd222 ..........Blank.................. 1 (PD changed to ddd222)
yyy ......................ttt............... Blank....................1 (another product,yyy)
yyy .....................ttt ...............Blank......................0 (same product and date, so not counting again)
View 9 Replies
View Related
