I am trying to use the sumproduct function with ranges defined in my VBA code. I can’t seem to get it to work.
I have two spreadsheets in the enclosed work book – T1, T2
T2 has four columns with the first three columns are text and the fourth column is an integer. The rows in this sheet may be repeated.
T1 has four columns similar to the first three columns in T2. However, the rows are unique. I am trying to do a sumproduct for obtaining the counts on the fourth column in sheet T1
To obtain the values for the fourth column (for each row) in T1 I do the following ...
Set ListB = Sheets("Sheet1").Range("B4:B22000") Set ListC = Sheets("Sheet1").Range("C4:C22000") Set ListD = Sheets("Sheet1").Range("D4:D22000")
MsgBox Evaluate("=SumProduct(--(" & ListB.Address & " = B4),--(" & ListD.Address & " = E1 ))") 'The above returns zero, which is not correct
Var1 = Range("B4").Value Var2 = Range("E1").Value MsgBox Evaluate("=sumproduct(--(" & ListB.Address & " = Var1), --(" & ListD.Address & " = Var2))") ' The above results in a Run Time 13 Type Mismatch error End Sub
I'm needing a macro that will allow me to get around the limits of no more than 7 IF statements and using a SUMPRODUCT formula as well. I need the total or sum of the macro/formula to be in cell "DB8".
Here's my formula: =SUMPRODUCT(IF(CP8=M11,EXACT(K7,"DI"),0)+0+SUMPRODUCT(IF(CP8=W11,EXACT(U7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AG11,EXACT(AE7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AQ11,EXACT(AO7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BA11,EXACT(AY7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BK11,EXACT(BI7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BU11,EXACT(BS7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CE11,EXACT(CC7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CO11,EXACT(CM7,"DI")+0,0))))))))))
The attached has a sumproduct formula that is slow down the calculation of my workbook. (I know there is also an array formula-that's another thread). I'd like to use a macro to fire on the worksheet change so I don't have the heavy recalc burden. The formula is in worksheet "Database" in column H. So far:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _ Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False) 'This formula below needs a VBA equivalent ' Target.Offset(0, 2).FormulaR1C1 = "=IF(RC[-1]>0,SUMPRODUCT(-(R7C7:R35000C7<0)*0.5,--(R7C3:R35000C3=RC[-5]))+SUMPRODUCT(--(R7C7:RC7>0),--(R7C3:RC3=RC[-5]),R7C7:RC7),0)" End If If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _ Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1) End If End Sub
I have a problem with a sumproduct formula which check one coloumn for negative numbers and another for a date so that it count all cases of negative numbers in a month i choose. (S contains numbers and AH contains dates)....
When i use it in a makro i get a "type mismatch" when i run the macro. I wonder if anyone can see where my syntaks is incorrect? Here is how it looks in the macro:.....
I want to compare 2 lists in separate sheets based on multiple criteria and delete the duplicates
Sheet 1 - new list in column A:E Sheet 2 - old list in columns B:F
So here is what I need: the macro should generate single IDs made of Sheet 1 Ai,Bi,Ci,Di,Ei cells for each row i to the end of the list + generate single IDs made of Sheet 2 Bi,Ci,Di,Fi
If . Evaluate (Sumproduct (IDs made of Ai,Bi,Ci,Di,Ei from sheet1) & Sumproduct IDs made of Bi,Ci,Di,Ei,Fi from sheet2) >1 then delete the entire row in Sheet 2.
This will leave me with only updated items (rows) in Sheet 2
=SUMPRODUCT(('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$E:$E=B4)*('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$D:$D=$A$2))
Which works perfectly if A2 has the cell value 24/03/2014 however, if i put in $A$2 this value
="02"&"/"&$XFC$1&"/"&"14", where XFC$1 equals =IF(XFD1>9,XFD1,"0"&XFD1) and XFD1 = =MONTH(TODAY())
Which in basic term gives the month value "03"
The cell shows the value 24/03/2014 however does not work with the sumproduct.. is there anyway to get sumproduct to read this cell value as 24/03/2014 as the whole point is so every month I don't have to update anything and it will constantly update...
The only alternative i can think of is quickly making a VBA script to hardcode the cell values for the dates and change the month value for the middle of the cells every month.
I am trying to use SUMPRODUCT on one of my excel files. the condition is that it will try to count all those cells that contain certain letters. i tried using this formula, =SUMPRODUCT(--('All Computers'!$A$2:$A$100000="USA*"),--('All Computers'!$E$2:$E$100000="June")), but I am getting a 0 value.
SUMIF allows one to enter a [valid] criteria text string into cell D2, say ">14000", and then reference D2 as the function criteria, e.g., =SUMIF(B5:B10,D2,C5:C10).
Is there a syntax that SUMPRODUCT will accept that does the same thing? Note the inclusion of a comparison operator is important.
=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01) I want to dis-include the CC="06" in the summation. I tried using: =SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)
A6 is a month column and looks like this "Jan-08" the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.
I have a spreadsheet with 3 coloumns. In A I enter Address, B Date, C Ward/District Area (this is a data validation list). At present I use; = SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2)). Summary B1 and B2 are dates to search between e.g 1/01/07 and 31/01/07. The above formula only counts the date enteries between B1 and B2.
I would like to change this so that it still checks that the date still falls between B1 and B2 but is adds together the numbers entered in coloum A. So coloumn A would become number of completed actions not an address.
In reference to an earlier thread, which shg and ByTheCringe2 both were genereous in their assistance, I would like to add a wrinkle, as requested by the end-users. how to reference an earlier thread for people to reference but you can search " Sumproduct By Last (criteria) Month With Data". Edit by ByTheCringe2: Sumproduct By Last (criteria) Month With Data.
We had used the MAX function to find the most recent investments, per counselor, per bank. I now need to find the most recent investments as of a certain date (month-end perhaps). I have ideas, but I'm afraid my misperceptions/assumptions might hinder me from seeing all available options. Initially, I thought I could use a text or listbox on the summary sheet to enter a date that would link to the reference cell on the data page, and this reference cell would then be used in whichever formula I came up with.
To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.
I am currently using this formula - =SUMPRODUCT(--(MONTH(Leads!B3:B999)=1),--(YEAR(Leads!B3:B999)=2013),--((Leads!J3:J999)="Contract"))
This gives me the count of rows that have Jan as the month, 2013 as the year and Contract word in column J. Now I want to add another column to be checked. That column has values in format - Design: X, Design: Y, Design: Z etc.
So I want to have the condition above and the cells that have the word Design.
1) I am doing multiple sumproducts on 6 sheets within a workbook with last row now being in the 12,000 cell range. Since putting in this SumProduct, my excel is extremley slow and affecting other windows applications. Is there anything I can use to replace this sum product ?
It seems to be starting the calculations even before I put any values in column D ? I have already tried the manual calculation, but it is not making much of a difference.
2) After doing the sumproduct, I then need another row to look at the value of the sumproduct cell and report it in another sheet. My problem is that the cell contains the date and time in 1 cell, but in order to get the cell to lookup the value properly I need to delete the 00 in the seconds to refresh the cell or else it gives me back an n/a# value, even though the seconds are 00 and not 01 or 02 etc ?
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
B17 is a keyword to be found in the Product_Keywords range
I'd like to modify it so that:
ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1
Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions, but how would I keep track of the number of commas in the cell?
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
Basically, what I'm dealing with is an inventory system. When a cell value reaches below a certain quantity, we have a cell light up with the word "YES" (under the "re-order" column). What I'd like to do is have a macro that checks to see if that cell is populated with "YES" and if it is, to run another macro (which I already have written) that sends out an email notifying us of the need to re-order.
How it would work: The person pulling out the inventory would fill out this form and click "Submit"
submitbutton.jpg
I would then have a "call" code tied to the "Submit" button that when clicked, would run the macro to check cell population and send out the email. The email code is already written and works flawlessly on it's own. I just cant seem to figure out how to write a macro that checks for cell value and then runs it (or not) based on that.
