Using SumProduct in VB

I was hoping someone can help me. 

I am writing a script that parses though an excel file and extract the 
unique entries and sums the quantities with respect to certain criteria (ie 
Month or Year). I currently have an excel formula that does what I am looking 
for, but I need to convert it to a VB script.

The formula looks like:

=SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))

Where C2:C30 contains the  item, E2:E30 is the month, and D2:D30 contains 
the quantity of the items. So this example counts the number of boxes from 
January in this data range.


In my script, I am trying to write:

Sheets("Summary").Cells(n, 4) = 
Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes") 
*(Range("E2:E30") = "January")* (Range("D2:D60")))

However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I 
have been unable to debug this statement. So I was hoping someone could help 
me with this.

I greatly appreciate any assistance you could provide.

Thanks,

-Michael
0
Michael5492 (655)
11/10/2005 4:51:24 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
514 Views

Similar Articles

[PageSpeed] 50

Use Evaluate

Sheets("Summary").Cells(n, 4) =
Activesheet.Evaluate("SUMPRODUCT(($C$2:$C$30=""boxes"")*" & _

"($E$2:$E$30=""January"")*($D$2:$D$30))")


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" <Michael@discussions.microsoft.com> wrote in message
news:900E9351-3AEE-4D9B-BAC5-62ED1FAF972D@microsoft.com...
> I was hoping someone can help me.
>
> I am writing a script that parses though an excel file and extract the
> unique entries and sums the quantities with respect to certain criteria
(ie
> Month or Year). I currently have an excel formula that does what I am
looking
> for, but I need to convert it to a VB script.
>
> The formula looks like:
>
> =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
>
> Where C2:C30 contains the  item, E2:E30 is the month, and D2:D30 contains
> the quantity of the items. So this example counts the number of boxes from
> January in this data range.
>
>
> In my script, I am trying to write:
>
> Sheets("Summary").Cells(n, 4) =
> Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> *(Range("E2:E30") = "January")* (Range("D2:D60")))
>
> However, when I run it, I get a "Run-time 13 error : Type mismatch" error.
I
> have been unable to debug this statement. So I was hoping someone could
help
> me with this.
>
> I greatly appreciate any assistance you could provide.
>
> Thanks,
>
> -Michael


0
bob.phillips1 (6510)
11/10/2005 5:30:41 PM
Michael,

Use, for example:

Dim myCount As Integer
myCount = Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2:$E$30=""January"")*($D$2:$D$30))")
MsgBox "There were " & myCount & " boxes shipped in January."

HTH,
Bernie
MS Excel MVP


"Michael" <Michael@discussions.microsoft.com> wrote in message 
news:900E9351-3AEE-4D9B-BAC5-62ED1FAF972D@microsoft.com...
>I was hoping someone can help me.
>
> I am writing a script that parses though an excel file and extract the
> unique entries and sums the quantities with respect to certain criteria (ie
> Month or Year). I currently have an excel formula that does what I am looking
> for, but I need to convert it to a VB script.
>
> The formula looks like:
>
> =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
>
> Where C2:C30 contains the  item, E2:E30 is the month, and D2:D30 contains
> the quantity of the items. So this example counts the number of boxes from
> January in this data range.
>
>
> In my script, I am trying to write:
>
> Sheets("Summary").Cells(n, 4) =
> Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> *(Range("E2:E30") = "January")* (Range("D2:D60")))
>
> However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
> have been unable to debug this statement. So I was hoping someone could help
> me with this.
>
> I greatly appreciate any assistance you could provide.
>
> Thanks,
>
> -Michael 


0
Bernie
11/10/2005 5:34:07 PM
Bernie,

Thank you. I do have one more question for you. How can I do the same thing, 
but be a little more modular:

Like
Evaluate("=SUMPRODUCT(($C$2:$C$30=Sheets("Inventory").cells(n,1))*($E$2:$E$30=""January"")*($D$2:$D$30))")

I want to use the same function for each item. I was playing around with it, 
but i could not get it to return the right value.

Thanks!

-Michael

"Bernie Deitrick" wrote:

> Michael,
> 
> Use, for example:
> 
> Dim myCount As Integer
> myCount = Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2:$E$30=""January"")*($D$2:$D$30))")
> MsgBox "There were " & myCount & " boxes shipped in January."
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Michael" <Michael@discussions.microsoft.com> wrote in message 
> news:900E9351-3AEE-4D9B-BAC5-62ED1FAF972D@microsoft.com...
> >I was hoping someone can help me.
> >
> > I am writing a script that parses though an excel file and extract the
> > unique entries and sums the quantities with respect to certain criteria (ie
> > Month or Year). I currently have an excel formula that does what I am looking
> > for, but I need to convert it to a VB script.
> >
> > The formula looks like:
> >
> > =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
> >
> > Where C2:C30 contains the  item, E2:E30 is the month, and D2:D30 contains
> > the quantity of the items. So this example counts the number of boxes from
> > January in this data range.
> >
> >
> > In my script, I am trying to write:
> >
> > Sheets("Summary").Cells(n, 4) =
> > Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> > *(Range("E2:E30") = "January")* (Range("D2:D60")))
> >
> > However, when I run it, I get a "Run-time 13 error : Type mismatch" error. I
> > have been unable to debug this statement. So I was hoping someone could help
> > me with this.
> >
> > I greatly appreciate any assistance you could provide.
> >
> > Thanks,
> >
> > -Michael 
> 
> 
> 
0
Michael5492 (655)
11/10/2005 6:02:05 PM
Evaluate("=SUMPRODUCT(($C$2:$C$30=""" & Sheets("Inventory").cells(n,1) &
""")*($E$2:$E$30=""January"")*($D$2:$D$30))")

assuming that it is text

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Michael" <Michael@discussions.microsoft.com> wrote in message
news:6C59FBA2-68B7-4466-AA46-2A1DC0EFFE2C@microsoft.com...
> Bernie,
>
> Thank you. I do have one more question for you. How can I do the same
thing,
> but be a little more modular:
>
> Like:
>
Evaluate("=SUMPRODUCT(($C$2:$C$30=Sheets("Inventory").cells(n,1))*($E$2:$E$3
0=""January"")*($D$2:$D$30))")
>
> I want to use the same function for each item. I was playing around with
it,
> but i could not get it to return the right value.
>
> Thanks!
>
> -Michael
>
> "Bernie Deitrick" wrote:
>
> > Michael,
> >
> > Use, for example:
> >
> > Dim myCount As Integer
> > myCount =
Evaluate("=SUMPRODUCT(($C$2:$C$30=""boxes"")*($E$2:$E$30=""January"")*($D$2:
$D$30))")
> > MsgBox "There were " & myCount & " boxes shipped in January."
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Michael" <Michael@discussions.microsoft.com> wrote in message
> > news:900E9351-3AEE-4D9B-BAC5-62ED1FAF972D@microsoft.com...
> > >I was hoping someone can help me.
> > >
> > > I am writing a script that parses though an excel file and extract the
> > > unique entries and sums the quantities with respect to certain
criteria (ie
> > > Month or Year). I currently have an excel formula that does what I am
looking
> > > for, but I need to convert it to a VB script.
> > >
> > > The formula looks like:
> > >
> > > =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30))
> > >
> > > Where C2:C30 contains the  item, E2:E30 is the month, and D2:D30
contains
> > > the quantity of the items. So this example counts the number of boxes
from
> > > January in this data range.
> > >
> > >
> > > In my script, I am trying to write:
> > >
> > > Sheets("Summary").Cells(n, 4) =
> > > Application.WorksheetFunction.SumProduct((Range("C2:C30") = "boxes")
> > > *(Range("E2:E30") = "January")* (Range("D2:D60")))
> > >
> > > However, when I run it, I get a "Run-time 13 error : Type mismatch"
error. I
> > > have been unable to debug this statement. So I was hoping someone
could help
> > > me with this.
> > >
> > > I greatly appreciate any assistance you could provide.
> > >
> > > Thanks,
> > >
> > > -Michael
> >
> >
> >


0
bob.phillips1 (6510)
11/10/2005 6:16:09 PM
Reply:

Similar Artilces:

Count if or SumProduct
I have two columns : column A defines the make/model; column B numerically defines how many were sold . Note: There are multiple entries for the same makes/models due to different territories. I have a separate worksheet for which I need to summarize this info. What would be the best formula for this task? I guess I am lost on what to enter to count. Any help is greatly appreciated! Thank you, Hi, Try this. The lookup value is in A1 of the currebt sheet =SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet1!B1:B20)) Mike "Brandy" wrote: > I have two c...

Cant drag and drop attachments using Outlook 2000
I am not a new user to outlook, but all of the sudden I am having problems dragging email attachments and dropping them on my desktop. I am running WinXP sp2 and outlook 2000. I can click on the attachment and save it to my computer, but i cannot drag it to any location. The only recent system change I had was installing MS antispyware, but this problem started several days afterwards. Any ideas? Matt, Look here: http://groups.google.com/group/microsoft.public.outlook/browse_thread/thread/9da97a6791fb0b6d/975f36dfc7e8bbe2?q=drag&rnum=16#975f36dfc7e8bbe2 Maybe your problem can actua...

Can you explain the difference & How to use: Combo Box/List Box?
Help! I would love to use a drop down field in my spreadsheet without having the data located on a separate sheet....I assume that the Combo Box and or List Box would do the trick....But I ABSOLUTELY can not figure out how to use them....CAN ANYONE HELP!?!? PLEASE! View the Controls command bar and drag a combobox on to your worksheet. Then right-click it, choose Properties and enter the range in the ListFillRange property, e.g., Sheet2!A1:A10 -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "SMGray" <SMGray@discussions....

Can't create OWNER-DRAW button using Create()
I have simple owner draw button derived from CButton with folowing functions : //-------------------------------------------------------------------------/ / BOOL CCustomButton::CreateNormal( CWnd *pParent, UINT nID ) { Create( _T(""), WS_CHILD | WS_VISIBLE , CRect(0,0,10,10), pParent, nID ); ModifyStyle( BS_TYPEMASK, BS_OWNERDRAW, SWP_FRAMECHANGED ); m_ToolTip.Create( this ); m_bCreated = TRUE; return TRUE; } //-------------------------------------------------------------------------- // void CCustomButton::PreSubclassWindow() { if ( !m_bCreated ) { ModifyStyle( BS_TYPE...

VB Panels in VC++
I have used panels to add loads of controls and I want to do the same in vc++. is this possible? You can create dialog resources and create modeless dialogs as children of the dialog that will host "panels". "Chris Baker" wrote: > I have used panels to add loads of controls and I want to do the same in vc++. > > is this possible? > Not sure what a VB "panel" is (I once wrote a VB program back in VB 3), but you might be talking about dockable toolbars or dialog bars. Try these keywords for searching the MSDN. joe On Mon, 14 Mar 2005 08:29:...

How to use Http to Down/Up load file?
Hello every, I need to use Http to upload and download file or directory. It's not easily to me because class not have CHttpFileFind, How can I use CListControl to express Http Server directory. and upload file from my computer to remote Http Server? I need and thanks for your help. i think, you can upload to use script language such as php. implements post by visual c++ and server side script language can help you. -- WebSite : Realization of Dream { imagine your dream} - http://rodream.net WebMail : rodream@naver.com "冰箱程式員" wrote: > Hello every, I need to us...

In ms word how to put symbols like 2/3? i dont want to use slash.
In ms word how to put symbols like 2/3? i dont want to use slash. i want to use a horizontal line. You can do this with an EQ \f field. See http://word.mvps.org/FAQs/Formatting/CreateFraction.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "adcodingmaster" <adcodingmaster@discussions.microsoft.com> wrote in message news:9269E403-DE1E-4BCF-A523-89041E2D9CDB@microsoft.com... > In ms word how to put symbols like 2/3? i dont want to use slash. i want > to > use a horizontal line. > ...

Can I insert hyperlink pictures into a document (to not use link)
I am using Doxygen which creates pictures and in its RTF format has links to those pictures. I want to have a document (will convert to WORD 2007) that has the pictures in it NOT the links. If each picture is saved as a separate graphics file (.jpg, .tif, .bmp, whatever), then you can simply browse to them and Insert them. On Mar 11, 12:48=A0pm, pmcg <p...@discussions.microsoft.com> wrote: > I am using Doxygen which creates pictures and in its RTF format has links= to > those pictures. I want to have a document (will convert to WORD 2007) tha= t > has the pictur...

How do I create a timeline using excel or powerpoint?
any help would be great-thanks! Jon Peltier has links to instructions for Gantt charts: http://www.peltiertech.com/Excel/Charts/GanttLinks.html Rich wrote: > any help would be great-thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi! Any information about what you are trying to do, how and with what problems would also be great. Alf -- AlfD ------------------------------------------------------------------------ AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4785 View this thread: http://...

Using Word XP as editor in Outlook 2003
My Outlook 2003 will not allow me to use Word XP as my default email editor (the option is greyed out). I tried using different email formats (HTML, rich text, plain text), but the option is not available. Any suggestions? You need to keep the pair in the same Office "family" to make it work. Hope this is useful to you. Let us know. rms Dan wrote: > My Outlook 2003 will not allow me to use Word XP as my > default email editor (the option is greyed out). I tried > using different email formats (HTML, rich text, plain > text), but the option is not availabl...

Getting HTREEITEM when using TVM_SORTCHILDRENCB
When using TVM_SORTCHILDRENCB, the compare callback function is passed the lParam (user-defined data) associated with the items. How can one retrieve the actual HTREEITEMs for the two items being compared? Thanks Bob "Bob S" <staheli.bob@gmail.com> wrote in message news:%23Nsr9UmLIHA.2140@TK2MSFTNGP03.phx.gbl... > When using TVM_SORTCHILDRENCB, the compare callback function is passed the > lParam (user-defined data) associated with the items. How can one retrieve > the actual HTREEITEMs for the two items being compared? > If the tree control has no native...

Use this package from the M$
--rejeemgc Content-Type: multipart/related; boundary="cgaquwoeown"; type="multipart/alternative" --cgaquwoeown Content-Type: multipart/alternative; boundary="gbueaxovlmyuc" --gbueaxovlmyuc Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to help ma...

Maintaining Text Box Font Setting through VB
Hi, Everyone - I've run into this issue, and I can seem to figure it out. In my spreadsheet, I've included several text boxes at the bottom of the pages. The VB code I also have included is, when the pages are printed, alternately place a "P" in the text boxes. Then I formatted the text boxes to use WingDings II so that checkmarks SHOULD appear in the boxes. I've also included code that would (1) unprotect the sheet; (2) print the pages with the text boxes; and (3) reprotect the sheet. What keeps on happening is that, for some unknown reason (at least to me), t...

Do we need to have license for VS 2005 when it is used with e conn
Hi, We have purchased e connect 9.0. We also need to use Visual studio 2005 along with econnect .Let me know whether we need to get license for Visual studio also at the time when we purchased e connect 9.0. (or) Should we get the license for Visual studio alone. A suggestion wud be appreciated. Thanx in advance, Kiran. There is a version of VS2005 that comes with SQL Server - that is what is used to build things like Integration Services and REporting Services REports etc. HS -- www.DynExtra.com A resource for the Microsoft Dynamics Community Featuring FAQs, File Exchange and ...

Remove extra Double codes from CSV Files (vb Script Language)
On daily bases I have received files with double quotes comma separated, but in some records I found 2 times double quotes and some time different squence of double quotes placements. I just want to remove all unnecessary (extra) double quotes (see the following values where i want to remove extra double quotes. "0123x",""Company D-Val"","Class D, sector N","DD5894" "4894D",""Recycle" Rubbish, C class","Class D, Sector F, Block N","D870GH" "AB8679",""AB Ltd"...

Using Two Computers
I use Outlook in pop3 mode. We have multiple computers downloading mail so we leave messages on the server until deleted. With Outlook 2000, when I wanted to move the messages to another computer, that has not been used for a while, I copied outlook.pst and put it on the other computer. This always takes the messages and contacts, especially the sent messages. What is less reliable is that sometimes the computer that I put the pst file on will download all the messages still on the server and sometimes it only downloads the new messages. Since there might be a few hundred me...

How to use Microsoft Outlook for Outlook Express User?
Does anyone have any suggestions on how to use Microsoft Outlook for Outlook Express User? For example, how to import Outlook Express folder into Outllook? how to change and move .pst file into different drive and folder? Any FAQ for Microsoft Outlook Thanks in advance for any suggestions Eric Outlook needs to be configured and working on the same PC as OE Within OE export msgs to Outlook http://www.howto-outlook.com/howto/backupandrestore.htm www.slipstick.com "Eric" <Eric@discussions.microsoft.com> wrote in message news:D34A08A1-6112-4CB3-B7A9-754813B4D...

Using Cell Data s Sheet Reference
I would like to have sheets named after a certain text string in a cell and refernce them in formulas For example: I have a sheet named '123Main' and a sheet named 'Main' On the 'Main Sheet' column 'B' is labelled 'Address' Cell B5 has text data '123Main' I want to be able to reference from Main!C5 a formula as such: ="whatever text is in cell Main!B5 is the sheetname to reference a cell on that sheet" I hope it is clear what I'm trying to do.......thanks. -- StompS Portland, OR http://www.geocities.com/pdxinvestr/Stomps.htm...

Using Worksheet Name
I am having multiple worksheets in an Excel file and now I want to use the name of each worksheet in a new worksheet in the same file. Is there a formula which can copy the name of worksheet into a Cell of another worksheet This article from Bob Phillips' site covers what you want: http://www.xldynamic.com/source/xld.xlFAQ0002.html Hope this helps. Pete On Apr 7, 1:49=A0pm, Akhil Goel <AkhilG...@discussions.microsoft.com> wrote: > I am having multiple worksheets in an Excel file and now I want to use th= e > name of each worksheet in a new worksheet in...

Can't use key and keyref with an attribute in a group
Hi, Why can't I create a key and keyref with an attribute declared in an attribute group? Code below describes what I'm trying to accomplish. <!-- An attribute group --> <xs:attributeGroup name="AttGMain"> <xs:attribute name="Name" type="xs:string" /> <xs:attribute name="Type" type="xs:string" /> </xs:attributeGroup> <!-- CT using group above --> <xs:complexType name="ctView"> <xs:all> <xs:element name="Control" type="ctControl" /> <xs:eleme...

settting up a temporary SMTP, while using POP3 Connector
Any help on this will be great. Background: Have SBS2003 with exchange 2003 installed. Our company MX records points to our ISP. The exchange 2003 uses POP3 connector to download users' emails. Our ISP is having problems and emails our arriving a day or a week after. Not all emails though, about 20 percent of them. ISP explanation of problem -- they are bombarded with spam, and emails that are not processed are going into their spooler server. This is where the delay occurs b/c the spooler doesn't send those emails fast enough -- they have problem here. My question: I woul...

Why I Can't use my document
I had save a document and now I can not work in it anymore and in anyother publisher saved documents, why that? What happens when you try? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Julie" <Julie@discussions.microsoft.com> wrote in message news:0F1BF475-99F1-4621-B66E-8641C37FBF5A@microsoft.com... >I had save a document and now I can not work in it anymore and in anyother > publisher saved documents, why that? Have you activated Publisher? Is Publisher a trial? -- Mary Sauer http://msauer.mvps....

Using eConnect for an eCommerce website
Is it possible to use eConnect to create an ecommerce website? We would be connecting to Great Plains so that the orders, customers, etc. could be created within Great Plains. If it is possible, how difficult is it to set up? kkemp wrote: > Is it possible to use eConnect to create an ecommerce website? We would be > connecting to Great Plains so that the orders, customers, etc. could be > created within Great Plains. If it is possible, how difficult is it to set > up? Hi, Yes, it is possible. If you wanted you could have the eConnect components on the web server and ...

How to create word style toolbar using MFC?
Hello All In MS Word, by default several toolbars are visible. Typically, each toolbar has several buttons, combo boxes etc. However, there are only few of them visible. There is an arrow button (usually on right end of the tool bar) clicking which a dialog consisting of other (non-visible) buttons shows up. This dialog also contains "Add Or Remove Buttons" menu item. The end user can use these to customise his toolbar. How to achieve this feature using an MFC toolbar? Does any of CToolBar, CToolBarCtrl, CReBar or CReBarCtrl or any other class provide such feature? Any help is appr...

How to use system speaker for new mail notification
How do i make Outlook 2000 use the system speaker for new mail notifications? We don't have external speakers and have heard other machines doing it.... ...