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

Use Trend Worksheetfunction On Vba Array

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

SUMIF (add The Values Of An Array)

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

Multiple Sumif Array

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

Sum With If Using Array Functions Or Sumif With VLookup

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

Array Formula With Sumif And Between Dates

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

SUMIF While Checking Each Array With Condition

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

Array Of Columns / SUMIF A Particular Column / Depending On A Certain Criteria

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

Array SUMIF Formula To Sum If Numbers But It Not Place Text?

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

SUMIF Or An Array Formula - Matching Multiple Conditions

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

SUMIF In Array Formula On Multiple Work Sheets

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

Worksheetfunction.vlookup Gives #VALUE! Error In VBA

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

Difference Between MAX And Application.WorksheetFunction.Max

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

VBA Application.WorksheetFunction.Match

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

Use Row Variable In Macro For WorksheetFunction

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

Worksheetfunction.text() And Font Colour

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

Get The Match Property Of The Worksheetfunction Class

Dec 7, 2009

Unable to get the Match property of the worksheetfunction class? Here is my code

View 5 Replies View Related

Runtime Error 1004 With Use Of WorksheetFunction

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

Application.WorksheetFunction.Match Error

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

Return Result Of WorksheetFunction To Cell

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

Cannot Get Correl Property Of WorksheetFunction Class

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

Unable To Get The VLookup Property Of The WorksheetFunction Class...

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

Unable To Get The Correl Property Of The Worksheetfunction Class

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

Unable To Get The Search Property Of The WorksheetFunction Class

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

Unable To Get The Vlookup Property Of The WorksheetFunction Class ..

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

Worksheetfunction.match Giving Run Time Error '1004'

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

Mismatch Error On A Big Application.Worksheetfunction.Match Code

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

Excel 2010 :: Unable To Get Mode Property Of WorksheetFunction Class

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

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

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

Nested SUMIF Statement Or Multiple SUMIF

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







Copyrights 2005-15 www.BigResource.com, All rights reserved