Hey Chandra,
try this
Function xxx(x, y)
Dim a, b, c As Range
a = Range("a4:a14") ' contains text
b = Range("b4:b14") ' contains text
c = Range("c4:c14") ' contains values
Range("a20") = Application.SumProduct((a = x) * (b = y) * c)
End Function
On Tue, Aug 30, 2011 at 11:56 AM, Chandra Shekar <
chandrashekarb....@gmail.com> wrote:
> Thank u very much got the solution.
>
> Please let me know is there any other way to do this.
>
> Thanks,
>
> Chandra Shekar B
>
> On Mon, Aug 29, 2011 at 5:49 PM, lifescholar
> <steve.ja...@lifescholar.net>wrote:
>
>> On Aug 29, 3:36 pm, Chandra Shekar <chandrashekarb....@gmail.com>
>> wrote:
>> > Hello,
>> >
>> > Could you please let me know whats the error in using sumproduct
>> function in
>> > below code. I am getting error number 2015
>> >
>> > Is there any other way to use sumproduct function using vba with
>> multiple
>> > condtions.
>> >
>> > Thanks in advance.
>> >
>> > Sub ABNAMROAdd()
>> > Dim compnm As String
>> > lastcl = ThisWorkbook.Worksheets("ABN AMRO").Cells(4,
>> > Columns.Count).End(xlToLeft).Column
>> > 'WeekNum = Weeknumber(Date)
>> > 'WeekNum = CurWk
>> > x = ThisWorkbook.Name
>> > y = ThisWorkbook.Worksheets(12).Name
>> > Z = "'[" & x & "]" & y & "'!"
>> >
>> > lastrow = ThisWorkbook.Worksheets("AR").Cells(Rows.Count,
>> 1).End(xlUp).Row
>> > rng1 = Z & "A2:A" & lastrow
>> > rng2 = Z & "C2:C" & lastrow
>> > rng3 = Z & "D2:D" & lastrow
>> > rng4 = Z & "E2:E" & lastrow
>> > rng5 = Z & "H2:H" & lastrow
>> > a = "SUMPRODUCT(--(" & rng1 & "=""" & "BEL" & """),--(" & rng2 & "=""" &
>> > "AAO" & """),--(" & rng4 & """))"
>> > cnt = Application.Evaluate(a)
>> >
>> > End Sub
>>
>> Can't speak for the logic of what you are trying to do, but your
>> SUMPRODUCT has an extra " mark in it which is what is causing the
>> error. Try:
>>
>> a = "SUMPRODUCT(--(" & rng1 & "=""" & "BEL" & """),--(" & rng2 & "="""
>> & "AAO" & """),--(" & rng4 & "))"
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
.........................
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
