Hi, It seems that Sumproduct doesn't work as expected in VBA. I have found that I need to use 'Evaluate'
So, if your worksheet formula was, for example:
SUMPRODUCT((A2:A200="Yes")*(B2:B200))
Then the VBA equivalent would be:
YourVariable = Evaluate("SUMPRODUCT((A2:A20=""Yes"")*(B2:B20))")
Note that if you are using text in one of the arguements, you have to double
the quotes.
ie, "Yes" becomes ""Yes""
If you want to use a variable rather than "Yes" then it's even more complex.
I've attached a workbook with both macros.
Hope this helps.
Regards - Dave.
Date: Fri, 2 Apr 2010 00:03:25 +0530
Subject: $$Excel-Macros$$ Sum Product use in Macro
From: nairra...@gmail.com
To: excel-macros@googlegroups.com
Hi Friends,
Can Sum Product use in Macro
Is it Possible to get some examples?
I am in trouble....
Please help me.
Regards,
Rajesh. Nair
--
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
_________________________________________________________________
New, Used, Demo, Dealer or Private? Find it at CarPoint.com.au
http://clk.atdmt.com/NMN/go/206222968/direct/01/
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe, reply using "remove me" as the subject.
Book1.xls
Description: MS-Excel spreadsheet
