|
Using "Evaluate" with Sumif
I am going bonkers!!!!! I am trying to use SumIf within the confines of the
Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Hi
not tested but try: xlApp.Range("k20") = Evaluate("=Sumproduct((" & rng1.Address & "=""F"")*(" & rng2.Address & "<""CLS"")*(" & rng2.Address & "))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Hi Bob
I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Set rng1 = .Worksheets(ShtRef).Range(cntref)
Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Sorry gentlemen about the range confusion, I should have checked my typing
better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Hi
first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
I have a formula that works when I enter it into a spreadsheet. I then tried
to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Hi
just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select
case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Hi
try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Frank, I am having trouble with your last bit of help. I tried to use your
suggested solution but I keep getting erros which highlight the brackets at he end of the statement. I have tried to search the web and the newsgroups but I have not been able to find a solution. I have tried: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4) and I have tried xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Any ideas on where I'm going wrong. I'm having trouble understanding the Quotation methods. "Frank Kabel" wrote: Hi try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Jeff,
Is it wrap-around? Try this xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Frank, I am having trouble with your last bit of help. I tried to use your suggested solution but I keep getting erros which highlight the brackets at he end of the statement. I have tried to search the web and the newsgroups but I have not been able to find a solution. I have tried: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4) and I have tried xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Any ideas on where I'm going wrong. I'm having trouble understanding the Quotation methods. "Frank Kabel" wrote: Hi try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
I tried your code Bob, and no errors concerning brackets and quotation marks,
but when I run it I get a "#Value" error. Now I have all three ranges set to be the same size, on the same sheet. When I run the code without the third argument I get a result. Any ideas as to where to look for solving this error? "Bob Phillips" wrote: Jeff, Is it wrap-around? Try this xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Frank, I am having trouble with your last bit of help. I tried to use your suggested solution but I keep getting erros which highlight the brackets at he end of the statement. I have tried to search the web and the newsgroups but I have not been able to find a solution. I have tried: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4) and I have tried xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Any ideas on where I'm going wrong. I'm having trouble understanding the Quotation methods. "Frank Kabel" wrote: Hi try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
All,
I finally found out what was missing in the SUmproduct equation. The last argument needed an Ampersand(&), Quotation mark(") and brackets [' & "))") '] as shown below. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4 & "))") Thank you all for your help on this matter, I do not know where else I would have found this information had it not been for the kindness and patience you have all shown me. Again, Thanks Jeff "Bob Phillips" wrote: Jeff, Is it wrap-around? Try this xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Frank, I am having trouble with your last bit of help. I tried to use your suggested solution but I keep getting erros which highlight the brackets at he end of the statement. I have tried to search the web and the newsgroups but I have not been able to find a solution. I have tried: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4) and I have tried xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Any ideas on where I'm going wrong. I'm having trouble understanding the Quotation methods. "Frank Kabel" wrote: Hi try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
Just a technique. For debugging, instead of
xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan ...etc try str = "=SUMPRODUCT(--('[Release Plan ....etc Then in your code: Debug.Print str Check the output. Copy this string to the workbook, and enter as an array formula. This should help w/ debugging. Another technique might be the following. Instead of vba variable names, make "CntRef3" a workbook name. ActiveWorkbook.Names.Add Name:="CntRef3", RefersTo:="Whatever..." Then [A1] = [SUMPRODUCT(--(CntRef ="F"),--(CntRef3 <"CLS")....etc ] -- Dana DeLouis Win XP & Office 2003 "Jeff" wrote in message ... I tried your code Bob, and no errors concerning brackets and quotation marks, but when I run it I get a "#Value" error. Now I have all three ranges set to be the same size, on the same sheet. When I run the code without the third argument I get a result. Any ideas as to where to look for solving this error? "Bob Phillips" wrote: Jeff, Is it wrap-around? Try this xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4) -- HTH <<snip |
Using "Evaluate" with Sumif
Jeff,
That was a tricky one due to the complexity of the formula that made it difficult for us to test. But, if we were able to get you along the right lines, and you solve it yourself, even better. Take a look a Dana's note on debugging techniques in this thread though, the principles could help you with future problems. Regards Bob "Jeff" wrote in message ... All, I finally found out what was missing in the SUmproduct equation. The last argument needed an Ampersand(&), Quotation mark(") and brackets [' & "))") '] as shown below. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4 & "))") Thank you all for your help on this matter, I do not know where else I would have found this information had it not been for the kindness and patience you have all shown me. Again, Thanks Jeff "Bob Phillips" wrote: Jeff, Is it wrap-around? Try this xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Frank, I am having trouble with your last bit of help. I tried to use your suggested solution but I keep getting erros which highlight the brackets at he end of the statement. I have tried to search the web and the newsgroups but I have not been able to find a solution. I have tried: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4) and I have tried xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Any ideas on where I'm going wrong. I'm having trouble understanding the Quotation methods. "Frank Kabel" wrote: Hi try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = ..Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks |
Using "Evaluate" with Sumif
An alternative:
Sub testIt2() Dim rng1 As Range, Rng2 As Range, Rng3 As Range Set rng1 = ActiveSheet.Range("a1:a12") Set Rng2 = Workbooks("book4").Sheets("sheet1").Range("b1:b12" ) Set Rng3 = ActiveWorkbook.Sheets("sheet3").Range("c1:c12") MsgBox Evaluate("SUM((" & rng1.Address(external:=True) & "=""f"")" _ & "*(" & Rng2.Address(external:=True) & "=""cls"")" _ & "*" & Rng3.Address(external:=True) & ")") End Sub (With the sub/end sub lined up! <vbg) Tushar Mehta wrote: Yeah, this is one of those cases where the judicious use of a function makes life a lot simpler. Not only that, but it will make maintenance a lot simpler. :) The following works just fine. Function fullAddr(rng As Range) fullAddr = "'[" & rng.Parent.Parent.Name & "]" _ & rng.Parent.Name & "'!" & rng.Address End Function Sub testIt() Dim rng1 As Range, Rng2 As Range, Rng3 As Range Set rng1 = ActiveSheet.Range("a1:a12") Set Rng2 = Workbooks("book4").Sheets("sheet1").Range("b1:b12" ) Set Rng3 = ActiveWorkbook.Sheets("sheet3").Range("c1:c12") MsgBox Evaluate("SUM((" & fullAddr(rng1) & "=""f"")*(" _ & fullAddr(Rng2) & "=""cls"")*" & fullAddr(Rng3) & ")") End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... All, I finally found out what was missing in the SUmproduct equation. The last argument needed an Ampersand(&), Quotation mark(") and brackets [' & "))") '] as shown below. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4 & "))") Thank you all for your help on this matter, I do not know where else I would have found this information had it not been for the kindness and patience you have all shown me. Again, Thanks Jeff "Bob Phillips" wrote: Jeff, Is it wrap-around? Try this xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef & _ "=""F""),--('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef3 & _ "<""CLS""),('[Release Plan (1,2,3,4).xls]" & _ ShtRef & "'!" & CntRef4) -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... Frank, I am having trouble with your last bit of help. I tried to use your suggested solution but I keep getting erros which highlight the brackets at he end of the statement. I have tried to search the web and the newsgroups but I have not been able to find a solution. I have tried: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4) and I have tried xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Any ideas on where I'm going wrong. I'm having trouble understanding the Quotation methods. "Frank Kabel" wrote: Hi try: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") Though if your CntRef are range objects you may have to use the following: xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef.address & "=""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3.address & "<""CLS""),'[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4.address )") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... Frank, ShtRef is a string, and is set as "Dec CPCT" Initially. I use a select case statement to set it to various sheet names. The same holds true for the CntRef except that it is set to a range such as "A3:A500" With everyone's suggestions I've succesfully got sumproduct to add using two arrays. When I try to use three as illustrated in the examples, I'm having trouble with the Quotes. xlApp.Evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " =""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef3 & "<""CLS"")*('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef4)") I've tried to match the Quotes and the brackets but I still get an error. Any pointers? "Frank Kabel" wrote: Hi just tell us what is behind your variables ShtRef, CntRef, etc. Just as a guess and not tested (assumption your variables are string variables and not object variables): msgbox application.evaluate("=SUMPRODUCT(--('[Release Plan (1,2,3,4).xls]" & ShtRef & "'!" & CntRef & " = ""F""),--('[Release Plan (1,2,3,4).xls]" & ShtRef&"!'" & CntRef & "<""CLS""))") -- Regards Frank Kabel Frankfurt, Germany "Jeff" schrieb im Newsbeitrag ... I have a formula that works when I enter it into a spreadsheet. I then tried to insert the variables for the path since the path has several options. =SUMPRODUCT(--([Release Plan (1,2,3,4).xls]" & ShtRef & CntRef & " = F),--([Release Plan (1,2,3,4).xls]" & ShtRef&"!" & CntRef & " < CLS))" But I can't seem to get it to work and the examples on that great page Frank sent me to only covers actual A1 type cell refs not those involving the substitution of path variables. Does anyone have a thought on this "Frank Kabel" wrote: Hi first try to create the formula directly in a cell (so you have a working basis). See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "JEff" schrieb im Newsbeitrag ... Sorry gentlemen about the range confusion, I should have checked my typing better. Anyway rng2 is different from rng1 and rng3. I am using offset and rng 1 to define rng2 and rng3 When I insert the example shown by Frank I get a error of #Value in my cell. ANyway, how best to use sumproduct to perform this function. I have only used it to count items in a countif situation. "Dana DeLouis" wrote: Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) Hi Frank. For the op, is rng1 and rng2 the same range? I might be wrong, but it looks like rng1 holds text (ie "=F"), so I'm not sure what Sum is being used on. Perhaps a different range that holds numbers. I agree that it looks Sumproduct might help if given the correct ranges. -- Dana DeLouis Win XP & Office 2003 "Frank Kabel" wrote in message ... Hi Bob I think both of us discovered the same bracket/apostrophe errors b ut in addition I think SUMIF won't work. Either the OP has to use SUMPRODUCT(... or SUM(IF(.... -- Regards Frank Kabel Frankfurt, Germany "Bob Phillips" schrieb im Newsbeitrag ... Set rng1 = .Worksheets(ShtRef).Range(cntref) Set rng2 = .Worksheets(ShtRef).Range(cntref) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & _ "=""F"")*(" & rng2.Address & "<""CLS"")," & rng2.Address & "))") End With -- HTH RP (remove nothere from the email address if mailing direct) "Jeff" wrote in message ... I am going bonkers!!!!! I am trying to use SumIf within the confines of the Evaluate method. Here is my code; Set rng1 = .Worksheets(ShtRef).Range(CntRef) Set rng2 = .Worksheets(ShtRef).Range(CntRef) xlApp.Range("k20") = Evaluate("SumIF((" & rng1.Address & "=""F"")*"& (" & rng2.Address & "<""CLS")," & rng2.Address & "))") I keep getting Compile Error:Expected")" and it Highlights "CLS", I've tried matching the barackets, checked my ""Quoteation marks -- Dave Peterson |
| All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com