Question about Microsoft Excel for PC

1 Answer

Sumproduct or conditional sumif with multiple criteria in vba code...

Can anyone of you excel vba wiz and gurus tell me what's wrong with this code, I keep getting a Run-Time error '13': Type Mismatch error with this code.

Private Sub CommandButton1_Click()

Sheets("summary").Range("g74").Activate

Dim i As Integer
i = 74
For i = 74 To 80

ActiveCell.Offset(0, 1).Value = WorksheetFunction. _
SumProduct(--Sheets("stock card").Range("a2:a3351").Value = Sheets("summary").Range("a" & i).Value, _
--Sheets("stock card").Range("b2:b3351").Value = "", _
--Sheets("stock card").Range("e2:e3351").Value >= Sheets("summary").Range("$g$84").Value, _
Sheets("stock card").Range("k2:k3351").Value)

ActiveCell.Offset(1, 0).Activate

Next i
End Sub

Also, I would like to be able to use the "conditional sumif" function using multiple criteria arrays in lieu of the above "sumproduct" function...is this possible???

Thanks in advance, any help is most appreciated...
Jay

Posted by on

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    Hot-Shot:

    An expert who has answered 20 questions.

    Corporal:

    An expert that hasĀ over 10 points.

    Mayor:

    An expert whose answer gotĀ voted for 2 times.

  • Contributor
  • 45 Answers

I'll just reply to your conditional sumif query.

yes, you can do multiple criteria query but with a matrix formula. you may look it up. nonetheless, here is how one would look like:

{=sum(if(range1=A x range2=B x range3=C, sum range))} note that x (multiplication) is used to denote intersection of the criteria

initially, you'll enter it as:
=sum(if(range1=A x range2=B x range3=C, sum range))

then, edit the cell (F2) and press ctrl+shift+enter for the brackets { } to appear.

Posted on Nov 18, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

SysTools VBA Password Remover serial


If you want to remove the VBA password from Excel, Access, Word, PowerPoint and Outlook etc with the help of VBA Password Remover Software rapidly.
http://www.vbapasswordremover.net/
http://www.systoolsgroup.com/vba-password-remover.html
http://www.vbapasswordremover.com/
http://www.vbapasswordremover.org/

May 25, 2011 | Excel-Tool Excel Tool VBA Password...

1 Answer

Why do i loose my custom Views in Excell 2003 when I add drop down lists?


Have you tried to save them as a custom worksheet template? See this tip , this one, this tip, and site for other tips related. (For saving custom views of filtering criteria, see here and here.)
Here is the MS article that thoroughly explains the means for using VBA codes to customize your excel 2003 command bars.

I hope this information allows you to resolve this issue. If you need further assistance, please post back with a comment to this thread.
If I've managed to answer your question or solve a problem, please take just a moment to rate this post....thanks!


Aug 26, 2009 | Microsoft Office Excel 2007

1 Answer

CODES for entering an I.D number using vba


Try the help built into Excel and give yourself time to absorb it; Excel has some of the best help in the business. VBA may be more complex than you need to solve a data entry problem. If you're trying to add data input masks in a database, why don't you try Access (the help for which is admittedly weaker because database programming is among the most complex of all productivity apps)? I learned VBA from 3 books I rented from a good public library over the course of a project that was about a month long, (because) at some point Microsoft abandoned the idea of teaching it via help screens. Still, you might join a MS developer site and those are loaded with detailed explanations and many other experts. I hope this helps... I feel your pain.

Mar 22, 2009 | Microsoft Office Excel 2003 for PC

2 Answers

Excel farmulas


Add another colum, say D with result of B*C

Do a list of the different cat, and use sumif
For cat 0101 (in cell F1)
formula to put in G1
=sumif($A$1:$A$7000;F1;$D$1:$D$7000) where F1 contain 0101

expand the formula for other cat. (G2, G3, etc)

You can use a assistant to extract the distinct cat from A1:A7000, sorting them and copy the result in F (Menu Data-> Filter -> elaborate filter)

Feb 16, 2009 | Microsoft Excel for PC

1 Answer

Excel counting


Use Pivot table, it might help you reach your target..!!

Aug 12, 2008 | Microsoft Excel for PC

1 Answer

SUMIF formula


One in general should not use Find & Replace.
I would recommend always creating the first formula manually by selecting the cells. This will avoid common errors like extra spaces.

May 11, 2008 | Microsoft Excel for PC

1 Answer

VBA Password


Can you read the code at all?

If yes, try copying the code to a new VBA screen and pasting it in.

Do your changes in this screen and save it as a Macro, then delete the original.

Note: I use a blank password on all my protected documents - not real good security, but easy to remember and others don't know that it is to be left blank - so it foils a lot of people. I use the password only to protect the formulas and script from being changed accidentally by the end user - not to prevent anyone from "stealing" my code.

VBA passwords seem to be difficult to crack and the programs to do so seem to be quite expensive for a one time use.

Have you thought of just rewriting the VBA script? Remember, you did it once and went through all the problems of working out the sequence etc so much of this might come back to you and save a lot of time over the original exercise.

Good luck.

Mar 26, 2008 | Microsoft Excel for PC

1 Answer

Excel formula


Yes, there is a function in MS Excel called "sumproduct" which multiplies the componenets in an array or arrays, then totals the result, eg :

=SUMPRODUCT(D4:D20,B4:B20) ...Job done...Enjoy !

Jan 16, 2008 | Microsoft Excel for PC

3 Answers

Excel vba password recovery


You can but you will need software. Search for google for VBA password recovery tools. Search for the appropriate crack you have it all . .

Dec 30, 2007 | Microsoft Excel for PC

1 Answer

Using if and then to add values to a cell


Try using the SUMIF function for one condition or multiple conditions. Use the SUMIF worksheet function to create a total value for one range based on a value in another range. For example, for every cell in the rage B5:B25 that contains the value "Northwind", the following formula calculates the total for the corresponding cells in the range F5:F25. Formula:
=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

Dec 09, 2007 | Business & Productivity Software

Not finding what you are looking for?
Microsoft Excel for PC Logo

3,905 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18304 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...