SUMPRODUCT Formulas Slowing Down Calculations
Feb 22, 2008
I am doing a report which analyzes a specific person's transaction per country (3 criterias). The workbook has 3 sheets. First sheet is the database, 2nd is the report itself and 3rd is a reference table. I am using a sumproduct formula to count per person's transaction per country. It is giving me slow calculations.
I cannot possibly use a pivot table since there will still be other calculations involved after determining the transactions. I tried using database functions but i can't seem to understand how to go about it when there are multiple criterias involved.
I am attaching a sample file for your reference.
View 6 Replies
ADVERTISEMENT
Jan 22, 2009
I have had a workbook that I have been using to track inventory. I use a userform to enter the data for inventory in and out of the system. I recently added two more worksheets to the workbook, each of which has several SUMPRODUCT formulas on them that reference the inventory data.
Since adding these two sheets, when entering data via the userform, it takes a long time (relatively speaking) for the sheet to update with the relevant new data. I have attached a copy of the file, in hopes that someone can show me exactly what is causing the slow-down, and if there is a way that I can get the same results using different programming or formulas that will not be so slow.
The part that is slow is on the "Data" sheet. Click on the green or orange buttons to open the userform. Make a selection in each section, and enter an amount. Clicking on "Enter" will transfer the appropriate information to the worksheet. This is the part that is slow.
View 4 Replies
View Related
Sep 25, 2009
My spreadsheet has a sheet1 with 1000 rows and needs four columns with "sumproduct" formulas referencing sheet2 with 20000 rows. An example of the formula is:
=SUMPRODUCT(--(Sheet2!$F$2:$F$20000=$B3),--(Sheet2$A$2:$A$20000 >=$J$2))
Once I add them, the recalculation slows it to a crawl. In trying to understand the problem, I do a Manual/Automatic between running a sort. The recalculation still takes too long to be practical.
View 9 Replies
View Related
Feb 18, 2008
I have a spreadsheet that has 1800 sumproduct formulas in it. Foe each day of the year it counts or sums 5 things. Each of these things has 2-3 criteria that is why I used sumproduct. The database it counts from is on the same sheet. It takes to long for the sheet to calculate. Is there a better way. I am using Excel 2003. The sheet itself is not huge 913 kb.
View 9 Replies
View Related
Jan 5, 2007
I made one excel file with 9 sheet. there are used sumproduct formula in 200 rows.
i am not used any vba macro for this file.
i am used sumproduct formula link to other files and other sheets.
my file size is 1.80 mb but when i am enter new data in file then file going in process of calculation and calculate all forumula it take some time. my file options calculation is automatic but for calculation it take time and i have to wait for that for Process.
There are any macro for calculation so my file work fast and don't want to wait for calulation process .
This file is not open like other files. it is take a time in update the link or calculation.
View 9 Replies
View Related
Aug 9, 2006
I was wondering if is possible to replace SUMPRODUCT with code. Right now I have formulas like these throughout my sheet and I think they are causing it to be slow:
=SUMPRODUCT(--($B$4:$B$1002<=B4),--($M$4:$M$1002="PROD"),--($O$4:$O$1002="O"))
I also have the following code in my worksheet. I don't know if anything here is causing it to take so long to "calculate cells." Also my code just stopped coloring the row ranges and I not sure what cause it to stop working.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "O:O"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'Begin coloring row ranges based on these requirements
If .Row > 3 Then
If Me.Cells(.Row, "O").Value = "" Or Me.Cells(.Row, "O").Value = "O" Or Me.Cells(.Row, "O").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If.................
View 6 Replies
View Related
Aug 23, 2007
I have the following formula in 220 cells in a workbook:
=( SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$H$1:$H$44995=$A5),'General Journal'!$I$1:$I$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$J$1:$J$44995=$A5),'General Journal'!$K$1:$K$44995))+(SUMPRODUCT(('General Journal'!$C$1:$C$44995>=$D$3)*('General Journal'!$C$1:$C$44995<=$E$3)*('General Journal'!$L$1:$L$44995=$A5),'General Journal'!$M$1:$M$44995))
Where D3 is a starting date, E3 is a closing date, and A5 is a sorting code. It looks at my general journal and looks for all the entries between the two dates, and then sums up all the entries linked to the sorting code in A5. The sorting code is in column H, and the amount to sum is in column I, and this is repeated 3 times.
You are already probably signering at how long I must have to wait while excel calculates all these formulas (* 220). I have resorted to a macro that turns caluculation to manual when I open this workbook, and back to auto when it closes.
View 8 Replies
View Related
Mar 16, 2012
Formulas are not working fine in VBA, They return wrong calculations. Here is the piece of code.
Range("S13").Select
Selection.FormulaR1C1 = "=SUM(COUNTIFS(C[-13],{""Closed"",""Open"",""Answered""},C[-9],{""Site from CDA"";""Site from LDR""},C[-11],""System""))"
'strr2 = Range("S13").Value
Range("R13").Value = strr1
Range("T13").Select
[Code] ........
View 1 Replies
View Related
Dec 24, 2007
I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.
View 6 Replies
View Related
Dec 10, 2012
I have a huge spreadsheet requiring me to manually enter alot of fields with formulas. I am using the formula
=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))
The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell) When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works
=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))
Is there an easier alternative then manually changing each number?
View 9 Replies
View Related
Dec 1, 2008
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.
I have a series of packages that are denoted by text phrases. Examples:
4/6/12
2/12/12
6/4/12
18/12
24/12
For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).
I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.
View 9 Replies
View Related
Aug 27, 2009
I want to say thanks for any help you might be able to offer. Attached are 2 sheets out of a workbook I am using which has many sumproduct formulas. On the the Input sheet, is where vacation and training forecast dates are listed. On the Calcs sheet, there are 2 monthly calendars for the year, 1 for vacation, the other for training. Would like to know if there is a way to have both records show up on only one calendar set. I currently have the vacation shown as a '1' and the training shown as a '9'. The workbook is used in Excel 2003 & 2007.
View 5 Replies
View Related
May 19, 2007
I've got a tab with 32K+ lines of data in six columns. I need to be able to summarize it in a table based on two criteria (e.g. Region, Line of Business). I am currently using 16 sumproduct formulas (=sumproduct((type=a5)*(reg=a4)*revenue)) where type is the line of business and reg is the region column, and 8 vlookups per region. Each tab will have about 8 regions and I'll have 4 tabs. As you can tell, this spreadsheet will quickly get out of hand in terms of calculating speed. I can't really use a pivot table because of: 1) formatting constraints 2) the vlookups refer to a separate tab than the main data dump. What alternatives do I have other than array formulas to get the data in a nice little summary form? The formulas/presentation aren't very complicated, but I have yet to come up with a good alternative to the above.
View 3 Replies
View Related
Oct 19, 2007
I was kindly provided with a formula from this forum a few months ago (in the attached workbook) to calculate the area under the curve of strings of data of varying length (i.e. different number of rows) these strings of data are aligned with a timeline (providing the Y and X axis for the curve respectively). The formula is able to calculate the area under these curves without the need for adjustment to the number of rows of the dataset.
However I have now tried to copy this formula to a new datasheet with a different total number of rows and a timeline fixed (of different intervals) in column A (rather than moving for each string of data) and I am receiving a #N/A error. I am sure that I have copied the formula correctly. Can anyone help fix my formula so that it calculates area under the curve again? I would like it to calculate the area under the curve for the data in the same column but using the fixed timeline in column A.
The working formula is on the sheet ‘original formula layout’ and the formula containing the error is on the sheet ‘new formula layout’.
View 4 Replies
View Related
Dec 13, 2006
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
View 4 Replies
View Related
Dec 18, 2006
I want to run through a loop to make it look as if my graph is animated. But the loop is too fast.
How can I slow down the loop to 1/10 of a second per calculation?
View 9 Replies
View Related
Jan 18, 2010
If the below formula is activated - e.g. something is put into cell f8 or g8, the I get the word calculate in the bottom left hand size of the s/sheet.
View 4 Replies
View Related
Jun 28, 2007
I would like to make a file that can calculate the quantity that need for the bakery shop, it's just my example. The customers can order the number of layers of the cake and that is related to the quantity from the table. I would like to looking for the 3 ingredients and the quantity in grams, and afterwards create a pivot table to sum by each type of the ingredients. The orders is about 3000 rows and ingredients are about 20 columns, If I use IF() and Vlookup() or put in Array formula, my program will be very very slow. Can I use others function to get the ingredients and the quantities? I have attached the file belows.
Please also make some suggestion, or something to read more, about the way I use the funtion like I use IF()+IF() or IF()&IF() instead of IF(,,IF). Does it make any different?
View 9 Replies
View Related
Aug 21, 2013
I have 2 sheets in a workbook
My VBA code gets data from a cell in sheet1 Then it paste that into a cell on sheet2 sheet 2 processes some data and produces a single cell result based on the pasted data Then that single cell result is pasted back into sheet1
Here is the problem. Sheet 2 takes a up to one second to produce the final result because it has to fetch some data from the web My code is pasting data back to sheet1 before sheet2 has had time to produce a final result
How can I make sure the data is finished on sheet2 before my code paste the result back to sheet1?
The variable MacdBlack is the being pasted back to sheet1 before sheet2 has had time to update it This is because sheet2 has to get data from the web which is slow
VB:
Sub SheetTest()
Dim x As Integer
Dim StartCell1 As String
[Code]....
View 6 Replies
View Related
Aug 10, 2014
I would like to add a "subroutine" to my macro to show the numbers it is picking. But I am having a problem figuring out how to do this.. Here is my macro...
Sub generatelottery2()
Const l& = 1 'lower value
Const u& = 49 'upper value
Const n& = 6 'number of numbers per draw
[Code]....
View 3 Replies
View Related
Dec 28, 2009
I have the following formula in a cell:
=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9
When the workbook with this formula is open it slows down other workbooks quite a bit.
If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.
How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.
View 9 Replies
View Related
Mar 30, 2009
I have a sheet that contains rows containing letter codes for example "CU". This is generated by an IF formula i.e. If true = "CU". However I need to be able to calculate the number of instances of "CU" but sumproduct will not work as it sees the cell as containing a formula not the result i.e. "CU". Sumproduct code:
View 3 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Dec 15, 2008
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies
View Related
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Jan 16, 2014
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies
View Related
Feb 9, 2010
if you look at attachment, in Cell J2, if cell I2 is greater than 2 minutes but less than two minutes, the I need the value of I2 in Cell J2 otherwise 0:00:00 then K2 between 3 & 4 Mins and so on.
View 5 Replies
View Related
Jan 27, 2010
I am making a spreadsheet that tells us the following information about prints that we do:
View 6 Replies
View Related
