Sumif WorksheetFunction On Array
Sep 18, 2007
does the SUMIF worksheetfunction in VBA work on Arrays? I know it will work on ranges, and thats all fine, but I am convinced it should work on an array too... for example... Just assume two columns of numbers (A and B) with 13 rows -
Public Sub test()
Dim a As Range, b As Range
Dim x, y
x = ActiveSheet.Range("A1:A13").Value
y = ActiveSheet.Range("B1:B13").Value
Set a = ActiveSheet.Range("A1:A13")
Set b = ActiveSheet.Range("B1:B13")
MsgBox Application.WorksheetFunction.Sum(x) 'works
MsgBox Application.WorksheetFunction.Sum(y) 'works
MsgBox Application.WorksheetFunction.SumIf(x, "1", y) 'wont work - "Object Required"
MsgBox Application.WorksheetFunction.SumIf(a, "1", b)
End Sub
I also tried Transposing the x and y arrays, but the same result "Object Required".
View 4 Replies
ADVERTISEMENT
Mar 3, 2007
I am trying to use the TREND worksheet function on a vba array. I keep getting Runtime error 1004: Unable to get the TREND property of the WorksheetFunction Class. I can bypass the error (like it were a search with no result), but I get no result of any kind from the function. My search of other threads yields nothing concrete, but causes a guess that it may have to be used on a worksheet range not a vba array. But that's not actually stated in any of the threads, and I find no documentation to that effect. Is this the issue or is it something else altogether?
Sub test()
Dim knowny As Variant
Dim knownx As Variant
Dim newx As Integer
Dim myval As Integer
knowny = Array(2, 4, 6, 8, 10)
knownx = Array(1, 2, 3, 4, 5)
newx = 6
'On Error Resume Next
myval = Application.WorksheetFunction.Trend(knowny, knownx, newx)
' 'next error, err as normal.
' On Error GoTo 0
' Err.Clear
Debug.Print myval
End Sub
View 5 Replies
View Related
Nov 5, 2008
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 2 Replies
View Related
Feb 7, 2008
I am preparing an analysis of my company's expenses for the last two years by account. One on excel sheet I have the raw data,
bank account, payee, payment date, clear date, amount, payment type
(This is from all accounts for the whole 2 year period.)
In the second sheet, I have a summary schedule setup. The summary is setup like this:
Account #
Row 1: Begin Date
Row 2: End Date
Payment Type 1 AAAAAAA
Payment Type 2 BBBBBBBB
I would like to come up with a formula that will populate AAAAA & BBBBB for a given account during a given time period. For example, I would like it to give me the total of Type 1 payments from account 12 during the period January 2, 2005 through January 27, 2006.
Right now it is only giving me the info for 1 payment type and 1 account. Here is what I have now {=SUM(IF(Detail!$G$10:$G$3942>=R$5,IF(Detail!$G$10:$G$3942
View 10 Replies
View Related
Apr 7, 2014
In sheet1 I have name(column A) and value(column B)
In sheet2 I have name (column C) and key(column D)
In sheet 3 I have results
Example ( It is just a total nonsense example, the real data is net inflow of some funds that are unique)
Sheet1
House 1000
Car 1500
pet 2000
Sheet 2
house1
car1
pet2
So in the sheet 3 I want to put a formula that is capable to sum all values of the sheet2 if the name has a key of 1in the sheet2, the key columns has values of 1 or 2
I think in SQL will be something with join and group by with having clause.
The result will be 2500 in the sheet 3.
What I tried: My attempt is to do something like this code ( using array functions).
View 7 Replies
View Related
Jan 15, 2010
I've hit a major brick wall and hoping someone will be able to help! I've written an array formula to replace a pivot table (long story) anyway, they now want to be able to filter the data by date (between two dates) i'm using the current formula:
{=SUM(IF('SAP Data Current'!$A$2:$A$39802='Payment Block by Ac. Clerk Cal'!$B$84,IF('SAP Data Current'!$I$2:$I$39802='Payment Block by Ac. Clerk Cal'!$C101,IF('SAP Data Current'!$B$2:$B$39802='Payment Block by Ac. Clerk Cal'!S$2,'SAP Data Current'!$AA$2:$AA$39802,0),0),0))}
I know I need to put it at the beginning, but not sure how! I have the following formula for between dates:
=SUMIF(B2:B24,"<="&F7,C2:C24)+SUMIF(B2:B24,"<="&F8,C2:C24)
but then this isn't working right either!
View 13 Replies
View Related
Jul 19, 2013
I currently have a Row with this value
row1 lh lh lh
row2 8 12 8 5 2
First I will need to check if the column has an "lh" if true then sum row2, however i will need a second condition that will check that if >8 it will use the value 8 instead of 12.
However if value is<=8 get that value from the cell.
In this case the correct answer is
8 + 8 + 5 = 21
8 (because it is <=8 get the value from the cell) + 8 (because 12 is>8 use the value "8") + 5 (because <=8)
View 3 Replies
View Related
Jul 24, 2014
I have a download from an accounting general ledger which has the following:
Column A: Category Description
Column B: Country
Column C: Department
Row 1: Months
Note: Each row contains the last 12 months worth of costs
As the categories/criteria can appear multiple times, and there are thousands of lines, I have been using SUMIFS to calculate totals my required combinations (eg. Travel expense, Germany, Sales department)
Every month, the information refreshes to show the most recent 12 months worth of data. And this means I need to manually update my formulas to correct the month column headings, as everything moves by 1 month.
Would something like a SUMIF with a SUMPRODUCT work? Eg. if current month = July, then it would sum everything from the July column automatically?
View 4 Replies
View Related
Feb 10, 2014
I have a list of Items and the quantities of those items on one sheet. Each item has a section location number as well. (three Columns, Item, Quantity, and Section Location). the list can repeat the same item multiple times.
This is why I then have a summary sheet to uses a SUMIF formula to sum the quantities of a given item that are in a givin section. which my SUMIF Formula works great for. But my problem is some items have the word "LUMP" as its quantity and not a number. I want to add to my array formula so if the item does have a LUMP quantity and the lump quantity is in the section location to put LUMP on the summary sheet. here is my formula
=SUM(IF((ProjectInfo!$AE$2:$AE$307 =$B59)*(ProjectInfo!$AD$2:$AD$307= AA$10),ProjectInfo!$AF$2:$AF$307,""))
projectInfo is the sheet where the list is. Column AE is the Item column on the projectinfo sheet column B on the summary sheet would be the item that i want to sum the quantities for Column AD is the section location column on the projectinfo sheet the AA$10 is the row and column of the section location i want to limit the sum of the quantities to the formula is in cell AA59 So it will sum all the quantities in the list on the projectinfo sheet if the item and section location match whats specified on the summary sheet.
getting LUMP to display if its a lump quantity. The Lump can only show up if the item on the projectinfo sheet has LUMP for that Item AND in that section location.
EDIT: Column AF on the ProjectInfo Sheet is the quantity column
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
Jan 3, 2012
I am trying to use the SUM IF Array formula to sum a group of numbers that fall under a heading of reference numbers over several sheets of data. For example purposes lets say my spreadsheet looks something like the below.
A
B
C
D
E
1
2600000248391
2600000393805
[code]......
The first two digits of the heading numbers are the criteria I am trying to use to separate and sum the data. For example I need to sum the value of the data below headings that falls between 1400000000000 and 1499999999999.
For the example above I used the below formula for the current Sheet and it works fine.
{=SUM(IF(A1:E1>="1400000000000",IF(A1:E1="1400000000000",IF(Sheet1!A1:E1,Sheet2!A1:E1
View 5 Replies
View Related
Nov 29, 2009
I've tried using the following (simplified) code to look up a date in a named range and return the result from the same row in the next column to the right. I can do this easily in the worksheet, but I can't write a VBA function to do it. Code:
View 2 Replies
View Related
Jul 2, 2008
what is the difference between them.
If i know how many rows there are in a column I guess i can just use MAX, right?
View 9 Replies
View Related
Jul 16, 2009
I have a large amount of data (10 columns X 1200+ rows). I want to copy some of the columns using the sorted names in the first column as a Match in the macro when the Matched cell (for comparison) is on a different sheet. I want to copy five (5) columns of data from the list on Sheet2 to Sheet1. The columns are B through F. I do not want to copy column A, but want to use the contents of the A column cells to determine which rows to copy.
I lock down the workbook and send it to other people and I want them to just pick a name from a drop-down list that will generate the Match cell on Sheet3 which will then populate Sheet1 with "Matched" data from Sheet2.
(I think I just confused myself.)
Part of my code is:
[code]
Sheets("Sheet2").Range("B1:F" & Application.WorksheetFunction.Match("XXXXX"),Sheets("Sheet2").Range("A:A"))).Copy Sheets("Sheet1").Range("A1")
[end code]
I want the "XXXXX" to refer to a cell on Sheet3. But I have been unable to solve the problem after trying a variety of fixes; including naming the single cell on Sheet3.
View 9 Replies
View Related
Nov 27, 2006
I am trying to add a small table after the last row using the data from the previous rows. I got the code workin as the following with some problems.
the result of the code below is as follows
"= countif(F4: F8," A ")"
"=countif(F5:F8," B ")"
"=countif(F6:F8," AB ")"
"=countif(F7:F8," O ")"
Herein lies a problem... I dont wish to have the values F4, F5,F6, and F7 change... rather, I want them to be constant at F5..............
View 3 Replies
View Related
Sep 4, 2009
The following code does everything I need it to except for some reason it is not sending the font colour through first or the font colour is getting overwritten. I have formula in other worksheets that are counting the red and the blue to determine which rider in a team it is. For some reason the time sent to "B Grade" worksheet is ending up in black font. Can anyone explain to me why this is occuring.
I am using worksheet function.text because I am working with elapsed times that could be over 24hrs. I was wondering if the default text colour for the worksheet.text() function is black and if it could be changed.
View 4 Replies
View Related
Dec 7, 2009
Unable to get the Match property of the worksheetfunction class? Here is my code
View 5 Replies
View Related
Dec 14, 2009
Few hours ago, I wrote a simple procedure in order to get an average of a range of 10 cells (A1:A10). Within the code, I typed these following lines:
View 4 Replies
View Related
Jun 26, 2008
why i get this error on
myTimeToCheck = (cHour & "." & cMinute)
myColCheck = Application.WorksheetFunction.Match(myTimeToCheck, Worksheets("Sheet3").Range("A2:A87"), 0)
MsgBox (myColCheck)
it says "Unable to get the Match property of the WorksheetFunction class"
myColCheck contains 17.50 and i have 17.50 in the column A
View 9 Replies
View Related
Sep 14, 2007
I am editing macro formula... is it possible VB does calculation during code execution & return only the value to Cell. this way the formula calculation remain hidden & code will probably run faster..eg.
Range("D3").Select
ActiveCell.FormulaR1C1 = "= COUNTA(Data!C[-3])-1"
Range("D3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I need to calculate & keep only value not formula in excel sheet each time the macro runs. I am getting error
Worksheets("data").Range("D3").Value = Application.CountA(Data![C:C]) - 1
View 3 Replies
View Related
May 10, 2008
I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.
View 2 Replies
View Related
Dec 4, 2009
I am trying to look at column at of two different workBOOKS and then when a match is found take the value from column D in workbook two and copy it to the corresponding row in the current column of workbook one.
this is the code I'm using and I get that unable to get the vlookup function. I thought maybe it was because not all of them will have matches so it could return an error if it is trying to return empty, so I put int on error resume next, and it ran through but never brought over ANY data, and there ARE matches and data to be brought over.
destRng is the range in the current column(that i just inserted) on the active worksheet that we will be putting the new info into. srcRng is the range in column A on the other workbook that the numbers are being compared to. src is just an integer
View 14 Replies
View Related
May 22, 2007
I get the "Unable to get the Correl Property of the worksheetfunction class" Error when I try to run the following code
CurrentRow = 2
CurrentColumn = 2
Finalrow = 5
WS2 = Sheet2
Finalrow2 = 5
Num_Records = 4
Correlation1 = Application.WorksheetFunction.Correl(Range(Cells(CurrentRow, CurrentColumn) & ":" & Cells(Finalrow, CurrentColumn)), WS2.Range("B" & Finalrow2 - Num_Records + 1 & ":B" & Finalrow2
View 9 Replies
View Related
Feb 23, 2009
I am having trouble with IsError function consistently catching errors in my call to the Search worksheetfunction. For example, here is a snippet of my code that works just fine:
Code: .....
View 9 Replies
View Related
Jan 4, 2007
I have a UserForm with one ComboBox and one TextBox. The ComboBox is populated from another sheet with product codes in Col A and names in Col B. When the user selects a product code from the ComboBox the respective name appears in the adjacent TextBox. Once both boxes have been filled the user can then save the data to a third sheet. Now the problem arises when the user tries to manually enter a code into the ComboBox that does not appear in its rowsource - VBA states a run-time error and my new keyboard goes flying out the window again. Attached is an example sheet
View 4 Replies
View Related
Mar 4, 2008
I am trying to used match function in the code but I am getting the following error:
Run time error '1004'
Unable to get the match property of the worksheet-function class
Here is the
View 9 Replies
View Related
Sep 23, 2008
bool = IsError(Application.WorksheetFunction.Match(ws1.Range("D" & i) & ws1.Range("G" & i) & ws1.Range("H" & i), ws2.Range("A3:A" & LR2) & ws2.Range("B3:B" & LR2) & ws2.Range("C3:C" & LR2), 0) > 0)
I am wanting to return a boolean result for if a multi-condition match is found. In pseudocode, I want to basically determine if the value in Column D, G, and H on worksheet 1 for a given row is repeated in Columns A, B, and C on worksheet 2 in any row. I have all variables properly dimmed.
View 13 Replies
View Related
May 3, 2013
If I type in the VBA Editor immediate window
Code:
?application.WorksheetFunction.average(array(1,3))
I get the expected answer of 2, but
Code:
?application.WorksheetFunction.mode(array(1,3))
produces a pop up error of:"Unable to get the Mode property of the WorksheetFunction Class"
(Excel 2010 on 64 bit machine)
View 2 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Sep 17, 2009
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
View 6 Replies
View Related