I have a sumproduct that evaluates # of conditions and returns the count when everything is met. But with the amount of data I have it is very slow to recalculate. I'm trying to find an alternative. I've read through a bunch of threads about different ways to avoid sumproduct but I can't get them to work. Here is the criteria I'm working with (simplified) .....
View 9 Repliesi have a large set of data and the sumproduct formula i have is extremeley slow ( half the time excel crashes)..plus i will need to be updating this most days!
this is the formula i have in each cell -
=SUMPRODUCT(--($L:$L=Y$3),--($D:$D=$N6),--($F:$F=$O6),($C:$C))
i have tried a sumifs version but it keeps coming up with zero.
would anyone have any help as to how i could get this for formula changed so that it isnt crashing my machine over a large range?
I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.
My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.
I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.
Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?
Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.
I use an Array Constant in an IF statement, rather than using OR?
I have tried the following without success:
=IF(A1={0,1},"Something","Something Else")
Which would look like this using OR:
=IF(OR(A1=0,A1=1),"Something","Something Else")
Is it possible? If so, what's the right syntax?
I have used the function INDIRECT in 1 of my files.
The disadvantage is that both files (source and target) have to be open.
Is there a substitute for INDIRECT that works with a closed source file?
I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:
View 2 Replies View RelatedI'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.
View 2 Replies View RelatedI have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.
Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...
Zone>Bank>Machine
then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.
Zone>Bank>Machine>Information on that machine
I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...
=IF(A164="A",ABankList,IF(A164="B",BBankList,IF(A164="C",CBankList,IF(A164="D",DBankList,IF(A164="E" ,EBankList,IF(A164="F",FBankList,IF(A164="G",GBankList,IF(A164="H",HBankList))))))))
Exit Sub alternative. I have the following
View 5 Replies View RelatedI am trying to make an IF statement and need to enter 12 if's. Excel will only let me go to 7. Example, if Jan is in AE11 the copy the number from B96, if Feb is in AE11 then copy number from c96. Everything works up to July.
View 2 Replies View RelatedI use a spreadsheet to keep track of stock at work.
The invoices are put into rows with the stock code and qty next to each other.
IE:
Invoice No | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty ect..
I am currently using an If function to check all the cells for an instance of each stock item in a total of four columns.
I was creating a formula in excel yesterday that used nested IF's but I soon found out that the maximum number you can use is 7. So I did a bit of digging and found out that I would be better of using a Select Case in VB.
What I want to do is this:
=IF((SEARCH("case1",A1)),"text1",IF((SEARCH("case2",A1)),"text2",IF((SEARCH("case3",A1)),"text",...etc until I have listed every possible case and I want to do this for a range of cells A1:A1000 or however many entries I have.
I am assuming I would have to use code similar to the solution in this thread:
[url]
The only thing is I cannot translate this to what I am doing. Do I need to write a loop to go through the entire range of cells I want to check? How do I determine if a specific string is contained within a cell? Once the macro is written do I refer to it from a cell or run it from within the macro menu? Please bear with me as I am new to this and have not coded in VB since school.
For each cell, instead using a comment, I need another way to show a large amount of principaly letter texts, without using hypervincules, and ofcourse comments.
By the way, the same text I enter or modify put in this "way", I need to modify in the MSWord document and viceversa.
I appreciate the linking do not open another app, because the large amount of them I need to work with.
Dim myRow As Long
Dim myCol As String
Dim Search As String
Sheets("Master Holdings").Range("H2")
Application.Screenupdating=false
mycol="E"
For myRow=6000 To 1 Step -1
If Activesheet.Cells(myrow, mycol).Value Search Then
Rows(myRow).Delete
End If
Next myRow
Application.ScreenUpdating=True
End Sub
Is there a better way to perform this code?
im trying to figure out how to loop or if there is an alternative to an if statement in this situation here...
in cell b2 i need to say
"if the height =< 12 then return 0
if 15==21 then 1
if 24==36 then 2
if height >=39 then 3"
I recieved on my last thread (link below), the code provided worked fine.
Previous thread I submitted
I now have a problem with the .value attribute. When comparing Excel finds two cells unequal in '.value' then writes '.values' in changes sheet. When using =A1=B1 the result is TRUE. I don't want equal valued cells to be picked up how can I stop this? (Macro code included in previous thread if needed)
I have this excel project that captures identification numbers using a scanner. once the scanner gets the info, it will mark the date/time the person was scanned. my problem is once the info scanned is not on the list, it will continue searching till i do an escape or ctrl-break.
heres the
Private Sub cmdOK_Click()
ActiveWorkbook. Sheets("list 2007").Activate
Range("F25").Select
Do
If ActiveCell.Value <> txtID.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = txtID.Value
ActiveCell.Offset(0, 2).Value = Now()
Range("F25").Select
txtID.Value = ""
txtID.SetFocus
End Sub
In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.
It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.
However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function
This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.
I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.
In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect..
I am trying to use the command below to report what type of bonus is to be awarded. .05% - 1% bonus, commission based.
=IF(E7>=0.12,1,IF(AND(E7>=0.115,D11<=0.1199),".09",IF(AND(E7>=0.11,E7<=0.1149),".08",IF(AND(E7>=0.10 5,E7<=0.109),0.07,IF(AND(E7>=0.10,E7<=0.1049),"0.06",IF(AND(E7>=.095,E7<=.099),".5",IF(AND(E7>=.09,E 7<=.0949),".04",IF(AND(E7>=.085,E7<=.0899),".03",IF(AND(E7>=.08,E7<=.08499),.025, IF(AND(E7>=.07,E7<=.0799),".15",IF(AND(E7>=.065,E7<=.0699),".005")))))))
It is telling me there are too many levels. I am not an Excel expert, so I am trying to figure out an alternative to this command, I am sure one is available.
Is there a more efficient method to what i have attached? i.e. if E4=anything between B2 and B10 then C2 is displayed. Between B11 and B16 then C11 is displayed.
View 4 Replies View RelatedThe below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:
K28 = Last month balance
Column E = Type of expenditure
Column D = Values
Column B = Payments
K23 = Currency conversion rate
During the execution of some code I come to a point where the properties of a range of cells needs to be converted to numeric (is at that point text).
Normally I do that by adding a 1 in an cell nearby and use the copy/paste special method.
I was wondering if I can do that in a different way.
Is it possible to define a MultiplyFactor as 1, and use this in paste special part.
Something like: .....
Does the following formula make sense and if not, what do I need to change? I'm receiving a "#VALUE" error. =MROUND(IF(A9=1,SUM(D1*0.1), 0) (IF(A9=2, SUM(D1*0.1/2), 0)), (IF(A9=3, SUM(D1*0.1/3), 0)), (IF(A9=4, SUM(D1*0.1/4), 0)),6)
It is possible to have multiple IF,THEN statements in one formula? A9 and D1 are the variables driving the formula. Let me know if I need to provide a detailed explanation.
I have a table of approx 20k customers (column A) and their $ sales (column B)for a time period. I need to sum the sales of approx 50 customers. I have the names of the 50 customers to be summed in one column so i can reference them and easily change out a customer if need be.
View 5 Replies View RelatedJust a curios question is it possible to have a button on a macro that if you press it would run one set of instruction, but if the user was holding a key down say the shift key and pressed the same button, it would run a second set of instruction
EG
If [Shift] key active then
Run command 1
else
Run command 2.
Sheet2ABCDE1Start Date 01/01/201005/01/2010Holiday211/01/2010Holiday3Days1445the date to be back to work 19/01/2010Excel 2010
Worksheet FormulasCellFormulaB5=WORKDAY.INTL(B1,B3,16,D1:D2)
but how i can get the same result with Xl 2003 or 2007
I have a workbook in Excel 2007 where I am creating a summation worksheet through the use of arrays which sumif and countif the other worksheets. Things went fine until about halfway through when I started getting merged cell errors. In between the calculations, I had some merged cell rows for section titles. I was not entering the array into them, but researched the error and unprotected the cells. Problem solved. Now, with 10 row to go, I started getting the error again. I unmerged the sectional title rows. I verified that the cells are not protected. However, when I ctrl+shift+enter to input the array, my formula is moving from the cell that I am inputting to, to the cell in the row directly beneath it.
View 3 Replies View RelatedI have 12 teams(rows) and would like a cell(A21) to show the team name after determining the max value of the column(O). Is there another way to do it besides "ifs", I've maxed out the function at 7, but there are 12 teams
If I could get this figured out it would be great, and a seperate question/addition problem I would also like to do is have the cell come up the same color as it is above.
I have are two listboxes set up, one with all the states, the other with 7 possible variable selections. So in my case I have 371 (DC, Virgin Islands and Puerto Rico are included as states in this) possible combinations here. With each combo the user needs to be able to enter up to 2 years of quarterly data (8 quarters, so 2968 pieces of data possible) and then press a button to save that data to a specified location which I will need to access at run of the overall macro.
Is there an easier way to do this then to simply assign each combination a cell? Maybe an If State = AK and Variable = TUR then find column = AK & row = TUR (per quarter) type solution?