Sumproduct Converted To VBA
Sep 25, 2008
I've got a rather large set of data consisting of many sheets of data. I need to gather some of this data in a summary for to make it presentable. (Common goal) Currently the summary page works on a series of sumproduct commands that reference other sheets and take a great length of time to compute. I'd like to change this into a VBA code to speed up the process and eliminate all of the Sumproduct referencing. I know that in VBA you have to use the execute command to sumproduct, but i continue to get errors that i cannot resolve.
This is a sample of on of the sumproduct formulas that works (though slow) in the sheet:
=-SUMPRODUCT((('June Data'!A2:A65535="18020")*'June Data'!I2:I65535)+(('June Data'!A2:A65535="18030")*(LEFT('June Data'!E2:E65535,2)="AF")*'June Data'!I2:I65535))
The Raw data comes in columns A thru L. I need to sum the values from column I when column A = "18020" or when column A = "18030" and the left to letters of column E are "AF".
When I input the sumproduct into VBA using the execute command I get a compile error, it says it expects a list seperator at the "18030" and doesn't like the quotes. When I remove the quotes or put single quotes the result i get is a NAME# in the cell. Any help would be appreciated. This is a clip of the VBA code. Also, anything that is faster than execute would be much appreciated. I suspect that even in VBA having to run 50 of these sums will take some time.
Range("q4").Select
Selection = Evaluate("=-SUMPRODUCT(('June Data'!A2:A65535="18030")*(LEFT('June Data'!E2:E65535,2)="AF")*'June Data'!I2:I65535)")
View 9 Replies
ADVERTISEMENT
Apr 13, 2009
I would like to have a code (VBA) for the below formula so I do not have to protect my worksheet (column B).
=IF(D15="","",INDEX(Sheet4!$C$14:Sheet4!$C$3000,MATCH(D15,Sheet4!$A$14:Sheet4!$A$3000,0)))
The above formula ends down until B13000. I feel the slowliness of Excel. I have seen something like this using the code (VBA) and it was not so slow compare with the formula created on the cell.
View 9 Replies
View Related
Nov 3, 2008
I work for a company that exports data from a website into Excel.
So here's my problem:
The data is usually Part Numbers using numbers and letters. When a Part Number is, for example, 248E12 we have this reaccurring issue. Importing it into Excel automatically converts it to scientific notation (ie. 248E+12 or 2.48E+14 to be exact). "Format Cell" doesn't convert it back because it was auto-converted when "exported". I need a way of fixing this problem without going line by line and doing the math to convert it myself.
I don't have time for that. Some of my files have thousands of lines.
View 14 Replies
View Related
Feb 29, 2012
I have converted a column of dates to days using format & dddd. However I'd now like to count how many mondays, tuesdays, etc are contained in this list, however as the data is still a date I cannot seem to do this.
View 6 Replies
View Related
Aug 13, 2009
I currently have formulas in a column setup (equations are not actual)...
Cell B1: =IF(A2>A3,"Y","")
Cell C1: =IF(B2>B3,''Y",''")
Cell D1: =IF(C2>C3,"Y","")
....
I want to switch these to one long row from one long column so that they span from Cell A1, A2, etc.
When I special paste or straight paste it obviously alters the formulas because I am also unable to cell lock the current formulas.
I am wondering if there is an easy way to convert my formulas, currently along a column, to a row? Or if there is also a way to cell lock a large amount of cells at once?
View 9 Replies
View Related
Jul 6, 2006
When I open a new excel workbook and enter a number in any cell it formats it as though I am entering a percentage. So when I enter 100, the cell value becomes 1. How do i change this? I have tried formatting the cells and no change....
View 3 Replies
View Related
Nov 28, 2013
I have adapted the use of StephenR's solution to a problem similar to that in the thread linked below. However I want to load the data directly into a new sheet instead of loading it into line 27 of the current sheet. This is the code I am working with, I think I need to change r2 somehow, not sure what to do exactly though.
[URL]
VB:
Sub KonverterFraMatrixTilListe()
Dim rng As Range, c As Long, r As Long, r2 As Long
r2 = 27
[Code]....
Another thing that I really want is for it to run not only for rows 4-8, but for rows 4-last row as defined by the A column.
View 7 Replies
View Related
Apr 24, 2014
I have a worksheet with a series of data in rows. Every 40th row is a sum of the previous forty rows.
I have a second worksheet that in a series of columns uses the 40th row sums from the other sheet. So column 1 uses row 40, column 2 uses row 80 data, etc.
Every time I update this, I create the new column using a copy and insert copied cells and then have to go to each field on the new column that uses this other worksheet to correctly update the formula for the new row. As when I do it this way, if Column 1 cell a1 formula said "worksheet2!A40, then the new column 2 cell b1 says "worksheet2!B40, where I want to make it be worksheet2!A80. I have tried highlighting a series of these corrected columns and dragging the formula over but still get a change in columns vs row from the other sheet.
I am looking to see if there is a way to do something like (in layman's expression):
(first column) worksheet 1, cell a1 = worksheet 2 cell A40
(second column) worksheet 1, cell b1 = worksheet 2 Cell A80
(third column) worksheet 1, cell c1 = worksheet 2 Cell (A80+40)
etc....
View 3 Replies
View Related
Apr 3, 2008
If I create a Workbook in Excel 2003 with Pivot Tables when I open it in 2007 the Pivot Table are no longer there. It look like the Pivot has been converted to Data only. Are there any ways of correcting this?
View 3 Replies
View Related
Mar 9, 2009
I have attached an example sheet to help visually explain what I am going to try and articulate. Cell A2 and B2's values result in cell E2, Cell C2 and D2's values result in cell F2. The same is true for row 3.
So what I would like to do is, based on the predetermined ranges of the Height+Chest Columns, auto complete column E, and Columns C+D, auto complete column F. My problem is that I cannot simply cell A + B = E, because the maximum range of one layers the minumum range of another. An example would be Maximum of A+B is 101 to result in SXS in Columm C, but the minmum A+B to get a SS in column C would be 97. So what I need is, for another specific example:....
If cell A2 is between 57 and 65, and cell B2 is between 31 and 36, then Cell C2 must be SXS. If Cell A2 is between 66 and 68, and Cell B2 is between 31 and 36, then Cell C2 must be SS.
View 5 Replies
View Related
Apr 18, 2014
I want some vbs code on my 'Copy All' button in Email Data sheet which will do my work. Its a bit lengthy but who knows the concept will find it easy.
I have one sheets -
Sheet attached - Worksheet .xlsm
Workshet.xlsm [Data in 'Mod' sheet i)should convert from text to column and paste in 'Row Data' sheet ii) then transfer form Row Data sheet to 'Email Data' sheet 'Mobile Data' sheet and 'Other Data' sheet when Copy All button is clicked in Email Data sheet.
There should be some logic which will fetch appropriate date from 'Mod' sheet to the respective sheet . We can have some kind of logic like
If 'Register' found then take the value of register and workstation and paste in Email Data sheet.
All data below should be fetched from Row Data sheet which is blank now and will get the data when we click the Copy All button ,
1st Register Count:
Below Register Count (*) i.e. 16 If workstation count present copy to E15 in Email Data sheet in Table B
Below Register Count (*) i.e. 9 If register count present copy to E16 in Email Data sheet Table B
2nd Register Count:
Below Register Count (*) i.e. 8942 If workstation count present copy to C15 in Email Data sheet in Table B
Below Register Count (*) i.e. 2883 If register count present copy to C16 in Email Data sheet in Table B
3rd Register Count:
Below Register Count (*) i.e. 0 If workstation count present copy to E18 in Email Data sheet in Table B
Below Register Count (*) i.e. 1 If register count present copy to E19 in Email Data sheet in Table B
4th Register Count:
Below Register Count (*) i.e. 98 If workstation count present copy to C18 in Email Data sheet in Table B
Below Register Count (*) i.e. 999 If register count present copy to C19 in Email Data sheet in Table B
Substr:
B32:B44 will be pasted in C29:C41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
B50:B62 will be pasted in B29:B41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0
View 1 Replies
View Related
Jan 8, 2013
I have a document that I downloaded some data from the Internet "Kronos" and within this data there is a column for date. This column is formatted as Date (01/08/13), but it only displays the date as Sun 1/01. It's missing the year, which it should be 01/01/12. I have tried to use TEXT function to convert it to month name and day name using TEXT(N4, "mmm") and Text(N4,"ddd"), but I still getting Sun 1/01. I have also tried to use Text to columns to split this date but, it does not work. Is there any other way I can convert this date into m/d/yy?
View 2 Replies
View Related
May 23, 2014
I have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
12/10/2013
12/9/2013
9/11/2013
9/9/2013
7/25/2013
View 9 Replies
View Related
Oct 21, 2009
1. I need to convert a year into a decimal year ie. 1830 into decimal year (I don't have a month, just year)
2 Year/month into decimal year/month
I just not sure what to do, is the year stored as a number/text/date. What should it even look like? Does 1830 display as 1830.00 using excel.
View 9 Replies
View Related
Dec 18, 2008
I have the following formula's:
SUMPRODUCT(($O$9:$O$259="Active")+0)+SUMPRODUCT(($O$9:$O$259="Clearing Tmrw")+0) and
SUMPRODUCT(--($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259) and
SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259)
My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)
The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.
However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.
Can anyone help me so that the formulas continue from row 9 ?
View 10 Replies
View Related
Jul 11, 2009
I have the following formula:
View 4 Replies
View Related
Mar 25, 2009
I have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.
I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....
View 3 Replies
View Related
Jul 11, 2012
How to add another sum to this formula?
this is the formula i have at the moment
=SUMPRODUCT(--($AA$21:$AA$6376
View 4 Replies
View Related
Dec 4, 2007
Here's my question.
jan- feb- mar- apr- may
rep a 1 1 2 5 1
rep b 1 2 7 7 7
rep c 5 5 1 2 2
rep d 1 1 1 1 1
rep e 2 2 2 2 2
rep f 1 2 3 4 5
rep g 0 0 0 7 9
date date
March May
How do I set it up so that I will have two cells that will contain a month. Once the 2 months are selected it will give me the total for each rep during that time period? Do I use sumproduct?
View 9 Replies
View Related
Aug 12, 2008
I have a report with a macro that imports a .TXT file into Excel then calculates the follwing formula. For some reason, before the .TXT file is loaded the formula is fine and has no errors, then when it is run I get a "#NAME?" error. I thought it might be because the formula changed during the macro but that's not it. Examples are below.
I've looked online and tried naming the ranges, but that doesn't work. I've tried the built in help but that didn't help. I've tried making sure the analysis pak is installed but that's not it either.
I've ran out of ideas now and you guys are my last hope. Any clues...??
Before macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status="AWAI")))
After macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status="AWAI")))
View 9 Replies
View Related
Aug 13, 2008
=SUMPRODUCT(OR(MIS!$T$4:$T$2042>500000)*(MIS!$U$4:$U$2042>500000))*(MIS!$Z$4:$Z$2042=C2)
it is if the cell in Column T >500,000 or the cell in Column U is more than 500,000. I keep getting a value error and I can't find a way around it.
View 9 Replies
View Related
Aug 26, 2008
I'm working on a project on predicting future cash flows from loans we have made and have ran into a wall as i can't figure out the formula to achieve what i would like to do. I've gotten a lot of help from you guys in the past and would appreciate any help you may be able to provide for this problem. So here it goes. First, here is how i have the spreadsheet setup.....
View 9 Replies
View Related
Nov 12, 2008
I've got a spreadsheet that had _many_ sumproducts and calls to vba functions and when it recalculated took some time to finish, so I thought I'd group the vba function with the sumproduct to see if it was faster, but I keep getting a #Value! error. The sumproduct layout worked fine on the actual spreadsheet, so I'm sure something is lost in translation. Here is my
Function weight(Aref As Range, count As Integer, Bref As Range) As Variant
Dim i As Integer
Dim top As Integer
Dim vA() As Variant
Dim cA() As Variant
Dim vR As Range
Dim cR As Range
Dim count As Integer
count = Widgets.Rows.count
ReDim vA(count - 1)
ReDim cA(count - 1)
top = Aref.Rows.count
For i = 0 To top - 1.............................
View 9 Replies
View Related
Apr 29, 2009
I seem to get a sum of 0 when I use this sumproduct syntax: SUMPRODUCT(($R$77:$R$442="H")*($Q$77:$Q$442="OTE"),(D77:D442)). What am I not doing?
Below is an e.g. of my data
CDEFGHIJKLMNOPQRAdmin4444440 ...
View 9 Replies
View Related
Jul 27, 2009
=-SUMPRODUCT((Catagory=$A26)*(MONTH(Paid_Date)=12)*
(YEAR(Paid_Date)=12)*(How="Till")*(How="Banking")*(Expenses))
This problem is I keep getting a #Value! error when I place the date section into formula. I have check to see thatthe date format is correct and as far as I can tell it is EXAMPLE: 06/12/2007
This formula did work in a nother worksheet, thus the confusion Ihave
The other issue is how to get two different text items in the "HOW" group to be True for Formula to pull requested values???
View 9 Replies
View Related
Apr 30, 2003
Now I am trying to build a user-defined function to use it.
It works in EXCEL but I can't get it to work in VBA
Function xxx(x,y)
Dim a, b, c As Range
a = Range("a4:a14") ' contains text
b = Range("b4:b14") ' contains text
c = Range("c4:c14") ' contains values
Range("a20") = Application.SumProduct((a = x) * (b = y) * c)
End Function
View 9 Replies
View Related
Jun 27, 2006
If B97:158=GL AND C97:C158=0001 THEN SUM E97:E158 OTHERWISE "0.00". I can't figure out the best formula to use.
View 2 Replies
View Related
Jun 12, 2007
sum with criteria. The solution was: = SUMPRODUCT(($A$7:$A$41=$A45)*($E$7:$E$41))
That was great, but, i need this every week, for a report, and not always all are in the same cells, and this formula is into a code, so the code loses sense when the week change. I tried to change the formula into a code, but it's impossible for me, although i have read all the post about it and sumproduct vba code. So, every week i use the same ranges in my code. So, for example:
A7:A41 in my code is:
Range(cells(7,1), cells(LastCell,1)
because i don't finish every week in the same row of column A
A45 is
Cells(LastCell + 4, 1)
I attach the same sheet of the other day
View 8 Replies
View Related
Apr 7, 2014
I am trying to arrive at the results in the yellow cells without needing the blue cells inbetween.
A sumproduct would be best but the 2nd row of blue cells has an if statement in it.
View 3 Replies
View Related
Jan 19, 2014
I am trying to do a sumproduct formula with hours and it is not calculating right.
Here is my current formula:
=SUMPRODUCT(([Productivity.xlsx]Occupancy!$A$2:$A$5000>=$C$1)*([Productivity.xlsx]Occupancy!$A$2:$A$5000<=$C$2)*([Productivity.xlsx]Occupancy!$B$2:$B$50ry00=$A4)*([Productivity.xlsx]Occupancy!$C$2:$C$5000))
My values in Column C that I am trying to get the sum of are:
1:24:49
:59:08
:58:46
1:39:19
1:10:08
1:13:15
1:04:23
1:43:04
1:23:55
1:48:11
1:42:41
The result of the sumproduct I am currently getting is 7824:00:00, however when I select the above values, Excel is showing it should be 13:09:45 in the status bar, which seems accurate.
I am not sure what I am missing to get this same number.
View 6 Replies
View Related
