Sumproduct formula works in spreadsheet, NOT VBA. Help please

Basically, I want to replace this formula with VBA Code

I can get this one to work to sum
=SUMPRODUCT(--(DataTime="First day of employment (Time 
1)"),--(DataPosition=N6),(DataQuestion1)

When I change this to Count rather than Sum, my Code Evaluates as an Error
=SUMPRODUCT(--(DataTime="First day of employment (Time 
1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))

The only line of code I switch out is the last mFormula row.  The line of 
code that works I've commented out.  I am really stumped...

This is the code that I have..

Dim mTimeCriteria As String
Dim mPositionCriteria As String
Dim mQuestion1Criteria As String
Dim mTimeRange As Range
Dim mPositionRange As Range
Dim mQuestion1Range As Range
Dim mFormula As String
Dim Kountifs As Variant   'could be an error
    
   mTimeCriteria = "First day of employment (Time 1)"
   mPositionCriteria = "Registered Nurse"
   mQuestion1Criteria = "*"
   
   With Worksheets("Data")
       Set mTimeRange = .Range("DataTime")
       Set mPositionRange = .Range("DataPosition")
       Set mQuestion1Range = .Range("DataQuestion1")

    mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) & 
mTimeCriteria & Chr(34) & "),"
    mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) & 
mPositionCriteria & Chr(34) & "),"
    mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34) 
& mQuestion1Criteria & Chr(34) & ") "
          
    'mFormula = mFormula & mQuestion1Range.Address & ")"     'This sums 
correctly
 
    ' MsgBox mFormula
    
    Kountifs = .Evaluate(mFormula)
    
      End With
    
    If IsError(Kountifs) Then
       MsgBox "Error in evaluating"
    Else
       MsgBox Kountifs
    End If
0
Utf
11/21/2009 2:35:02 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
456 Views

Similar Articles

[PageSpeed] 39

Hi

You miss a closing paranthesis in your formula.

>    mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> & mQuestion1Criteria & Chr(34) & ")" ' <=== Two closing paranthesis needed 
> here.


Regards,
Per



"DogLover" <DogLover@discussions.microsoft.com> skrev i meddelelsen 
news:B09154BB-93F2-4C2F-BAC5-1DE75AF001DE@microsoft.com...
> Basically, I want to replace this formula with VBA Code
>
> I can get this one to work to sum
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition=N6),(DataQuestion1)
>
> When I change this to Count rather than Sum, my Code Evaluates as an Error
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
>
> The only line of code I switch out is the last mFormula row.  The line of
> code that works I've commented out.  I am really stumped...
>
> This is the code that I have..
>
> Dim mTimeCriteria As String
> Dim mPositionCriteria As String
> Dim mQuestion1Criteria As String
> Dim mTimeRange As Range
> Dim mPositionRange As Range
> Dim mQuestion1Range As Range
> Dim mFormula As String
> Dim Kountifs As Variant   'could be an error
>
>   mTimeCriteria = "First day of employment (Time 1)"
>   mPositionCriteria = "Registered Nurse"
>   mQuestion1Criteria = "*"
>
>   With Worksheets("Data")
>       Set mTimeRange = .Range("DataTime")
>       Set mPositionRange = .Range("DataPosition")
>       Set mQuestion1Range = .Range("DataQuestion1")
>
>    mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
> mTimeCriteria & Chr(34) & "),"
>    mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
> mPositionCriteria & Chr(34) & "),"
>    mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> & mQuestion1Criteria & Chr(34) & ") "
>
>    'mFormula = mFormula & mQuestion1Range.Address & ")"     'This sums
> correctly
>
>    ' MsgBox mFormula
>
>    Kountifs = .Evaluate(mFormula)
>
>      End With
>
>    If IsError(Kountifs) Then
>       MsgBox "Error in evaluating"
>    Else
>       MsgBox Kountifs
>    End If 

0
Per
11/21/2009 9:00:52 AM
Thanks.  That was it.  

"Per Jessen" wrote:

> Hi
> 
> You miss a closing paranthesis in your formula.
> 
> >    mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> > & mQuestion1Criteria & Chr(34) & ")" ' <=== Two closing paranthesis needed 
> > here.
> 
> 
> Regards,
> Per
> 
> 
> 
> "DogLover" <DogLover@discussions.microsoft.com> skrev i meddelelsen 
> news:B09154BB-93F2-4C2F-BAC5-1DE75AF001DE@microsoft.com...
> > Basically, I want to replace this formula with VBA Code
> >
> > I can get this one to work to sum
> > =SUMPRODUCT(--(DataTime="First day of employment (Time
> > 1)"),--(DataPosition=N6),(DataQuestion1)
> >
> > When I change this to Count rather than Sum, my Code Evaluates as an Error
> > =SUMPRODUCT(--(DataTime="First day of employment (Time
> > 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
> >
> > The only line of code I switch out is the last mFormula row.  The line of
> > code that works I've commented out.  I am really stumped...
> >
> > This is the code that I have..
> >
> > Dim mTimeCriteria As String
> > Dim mPositionCriteria As String
> > Dim mQuestion1Criteria As String
> > Dim mTimeRange As Range
> > Dim mPositionRange As Range
> > Dim mQuestion1Range As Range
> > Dim mFormula As String
> > Dim Kountifs As Variant   'could be an error
> >
> >   mTimeCriteria = "First day of employment (Time 1)"
> >   mPositionCriteria = "Registered Nurse"
> >   mQuestion1Criteria = "*"
> >
> >   With Worksheets("Data")
> >       Set mTimeRange = .Range("DataTime")
> >       Set mPositionRange = .Range("DataPosition")
> >       Set mQuestion1Range = .Range("DataQuestion1")
> >
> >    mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
> > mTimeCriteria & Chr(34) & "),"
> >    mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
> > mPositionCriteria & Chr(34) & "),"
> >    mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> > & mQuestion1Criteria & Chr(34) & ") "
> >
> >    'mFormula = mFormula & mQuestion1Range.Address & ")"     'This sums
> > correctly
> >
> >    ' MsgBox mFormula
> >
> >    Kountifs = .Evaluate(mFormula)
> >
> >      End With
> >
> >    If IsError(Kountifs) Then
> >       MsgBox "Error in evaluating"
> >    Else
> >       MsgBox Kountifs
> >    End If 
> 
> .
> 
0
Utf
11/21/2009 1:09:01 PM
Check your other post.

In fact, if you're going to repost your question, please go back to the other
threads and say that you reposted.

That way, others won't waste their time duplicating answers.

DogLover wrote:
> 
> Basically, I want to replace this formula with VBA Code
> 
> I can get this one to work to sum
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition=N6),(DataQuestion1)
> 
> When I change this to Count rather than Sum, my Code Evaluates as an Error
> =SUMPRODUCT(--(DataTime="First day of employment (Time
> 1)"),--(DataPosition="Registered Nurse"),--(DataQuestion1<>"*"))
> 
> The only line of code I switch out is the last mFormula row.  The line of
> code that works I've commented out.  I am really stumped...
> 
> This is the code that I have..
> 
> Dim mTimeCriteria As String
> Dim mPositionCriteria As String
> Dim mQuestion1Criteria As String
> Dim mTimeRange As Range
> Dim mPositionRange As Range
> Dim mQuestion1Range As Range
> Dim mFormula As String
> Dim Kountifs As Variant   'could be an error
> 
>    mTimeCriteria = "First day of employment (Time 1)"
>    mPositionCriteria = "Registered Nurse"
>    mQuestion1Criteria = "*"
> 
>    With Worksheets("Data")
>        Set mTimeRange = .Range("DataTime")
>        Set mPositionRange = .Range("DataPosition")
>        Set mQuestion1Range = .Range("DataQuestion1")
> 
>     mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & "=" & Chr(34) &
> mTimeCriteria & Chr(34) & "),"
>     mFormula = mFormula & "--(" & mPositionRange.Address & "=" & Chr(34) &
> mPositionCriteria & Chr(34) & "),"
>     mFormula = mFormula & "-- (" & mQuestion1Range.Address & "<>" & Chr(34)
> & mQuestion1Criteria & Chr(34) & ") "
> 
>     'mFormula = mFormula & mQuestion1Range.Address & ")"     'This sums
> correctly
> 
>     ' MsgBox mFormula
> 
>     Kountifs = .Evaluate(mFormula)
> 
>       End With
> 
>     If IsError(Kountifs) Then
>        MsgBox "Error in evaluating"
>     Else
>        MsgBox Kountifs
>     End If

-- 

Dave Peterson
0
Dave
11/21/2009 1:35:18 PM
Reply:

Similar Artilces:

simple drag & drop not working
Hi, If I start dragging, I always get the "DropEffect - None" icon, means that drop is not possible. In my CFormView::OnCreate I already did a dropTarget.Register(this). void Simulation::OnLButtonDown(UINT nFlags, CPoint point) { this->clickedObject=GetDocument()->IsObjClicked(point); if(this->clickedObject!=NULL) { COleDataSource* ds=new COleDataSource(); CSharedFile file; CArchive ar(&file, CArchive::store); this->clickedObject->Serialize(ar); ar.Close(); ds->CacheGlobalData((CLIPFORMAT)RegisterClipboardFormat("Test"), file.Detach());...

macro for work book
How can I write a macro to have it refresh data and text to column/ fixed width How can I develop this macro not for a sheet but for a work book. Thanks Daniel Daniel; Hate to say so, but I don't understand anything of your question. Also, what does this have to do with "Excel charts"? -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Daniel" wrote: > How can I write a macro to have it refresh data and text to column/ fixed width > > How can I develop this macro not for a sheet but for a work book. > > Thanks > Daniel > ...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Help??? Products
I think I broke something. We have a fairly "vanilla" install of crm 1.2 and all is working fine, however, today I looked at the utilizing the products page and I noticed that I cannot create new products. The button is missing. I have administrator permissions, and the security role is defined with full permissions to everything. I had created 3 "test" products previously, so, I know it did work. What might be wrong? thank you for your time, ...

Conditional Formatting Formula Help Part 2
Hi, I am working on a conditional formatting formula that will refer to a different worksheet that the one it is in, I hope this can be done, so here's how the formula needs to be: in cell X2 of worksheet "10 DAY AVERAGES" I need a formula that tests cell W2 in worksheet "PASTE DATA" to see if it's greater than the 20 day average of cells W2 through D2 in worksheet "PASTE DATA", and if so I will program a certain highlighting color via conditional formatting, I would appreciate very much some help with this formula THANKS! -- RalphSE ---------------...

how to get boost regex library working in vc6[eom]
how to get boost regex library working in vc6. "raj s" <yesraaj@gmail.com> ha scritto nel messaggio news:9770ab84-a103-423f-ba83-f621082d97f4@d1g2000hsg.googlegroups.com... > how to get boost regex library working in vc6. Do you have any particular compilation error? Boost is a complex library, that uses advanced features of C++ (including template advanced techniques, like template metaprogramming). VC6 compiler is not very good to handle advanced C++ template techniques, so I would not be surprised if some Boost template classes failed in compilation with VC6... ...

formula #37
I have 23 in column 1. (This is the standart) Black color 22 in column 2. want to make diffrent color automatically 25 in column 3. black color what can i do If I want diffrent color when the nominal less then standar (22 change to diffrent color automatically) Hi Kevin use conditional formatting click on the 22 in column 2 - assuming cell reference B1 choose format / conditional formatting from the first drop down box: cell value is from the second drop down box: less than in the white line: =$A$1 - adjust the A to reflect the "correct" column reference click on the format ...

Importing Works database to excel
Hi to all I am trying to import a works database into excel. I can convert database to CSV etc buton importing to or opening in excel the text in each cell is truncated to 256 characters. If I paste 1 cell from Excel into say Word I only get 256 characters. I have researched this on the internet but cannot find the answer. What is is the solution? I am using Excel from Office 97 Cheers Don Strachan For some 10,000 NZ and international Automotive, Alternate Fuel and Motorsport Links and information. Dons Auto Pages: <http://www.donsautopages.co.nz/> Email: <don@NOSPAMdo...

Inserting formulas
Is there a way to have an If/Then statement insert a formula? For example, =if(A5=1,10,"=if(P27>5,"",0)") Thank you Try =IF(A5=1,10,IF(P27>5,"",0)) HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Adam1 Chicago" <Adam1Chicago@discussions.microsoft.com> wrote in message news:84060A73-4499-4CF4-814B-026740AEF8A5@microsoft.com... > Is there a way to have an If/Then statement insert a formula? For example, > =if(A5=1,10,"=if(P27>5,"",0)") > > Thank you Put the formula in an...

Hyperlinks not working
I have used the "web page preview" option before publishing my website and all appears fine. However, once I publish my website, any hyperlinks that previously worked on one page no longer are working. I need help with figuring out what is causing this. I am using 2000. My url is as follows for this site: http://pvhs.noacsc.org/panther_productions/page7.html They seem to work here. Are you on dialup to connect to the Internet? If so, could be your files are very large and it will take time for them to load. -- Don ------- Vancouver, USA "JMenge" <JMenge...

Formulas coming up as text
Formulas continue to appear as text and will not calculate. how to get formulas to always calculate. If your formulas appear as text, then you need to first clear the cells before entering formulas. Select the cells, hit Edit-->Clear-->All. Save the file, format the column as General, and try again. ******************* ~Anne Troy www.OfficeArticles.com "cheryl" <dianeshar14donotspam@example.com> wrote in message news:D6AAAAFF-9E92-4947-837F-9FA96984BD4D@microsoft.com... > Formulas continue to appear as text and will not calculate. how to get > formulas to alway...

[Help] Tape Backup Developing
In order to develop tape backup program under Windows, I have just read "Microsoft Tape Format v1.0", and think I should follow the guidance of MTF. The question is: 1) There are many complex control structures (as many as 8 types of HEADERs) to store information such as volume, directory and file name; 2) But the Windows API only provides very simple (low level) Read/Write operations. Does it means that I must manually fill out those structures in accordance with the MTF and write them to the tape one by one? That will be a very big burden to us programmer! Any suggestions or re...

IF,AND Formula
Trying to get a cell to show a 1 (so I can add it), IF cell A1 is blank and A2 thru A6 has an "f" in any of them. If/AND formulas are killing me! Your help is appreaciated. Try this: =IF(AND(A1="",COUNTIF(A2:A6,"f")>0),1,"") You didn't say what value you wanted if the 2 conditions are not met so I assumed you wanted a blank cell. Change the "" before the last bracket to something else if a blank cell is not what you want. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: h...

Looking for help re chart speed after upgrade to Excel 2007
Upgraded to XL2007 last week. Worksheet charts graphing systems administration data that have worked fine for years are now so slow as to be almost unusable. I see a lot of postings about the problem but not very many solutions. Seems XL2007 has been out long enough for there to be a solution out there somewhere. Is there a FAQ or something outlining solutions for this someone could point me to? I hate to reinstall O2K3 but will have to if cannot find solution quickly. As far as I know there is not yet a solution for the charting slowness of Excel 2007. There have only been security re...

POS Buttons -- Click to Show Work Orders
How can I program the custom POS Buttons to execute Program Tasks... For example, how could I have the button show the Work Orders screen of RMS application? Another need might be to open up Lay-Aways. Etc., etc.. Thanks, D in manager go to database*Registers*Custom POS buttons create NEW Number next available style = Internal Command Caption = Create Work Order Command = createworkorder image can be anything you want for layaway would be command =createlayaway "DonS" <DonS@discussions.microsoft.com> wrote in message news:48532B28-424D-4DC8-B76F-43CEFC1220E4@micros...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Can anyone help ?
I have created a holiday planner for staff with in are company and i need a formula that gives us only 10% of the total number of staff are off on holiday. would be greatful if anyone could help. Hello - If you have a total somewhere (I would suggest inserting a column on your spreadsheet titled Total and then entering a "1" if the person is going to be out, then total the column of "1"s by entering "=SUM(x:y)" where x=first cell in the range, and y=last cell in the range), in a different cell, enter "=.1*z" where z equals the total of people out...

How do I stop a formula from rounding up to the next number
What I am trying to do is take a cell and find 6.2% of it by =(SUM(B19*C19)) then adding the cells to other cells accross without it rounding up to the next higher number. This is for payroll deductions. Change the cell format to NUMBER and however decimal places you want Right click on the cell and FORMAT should be an option -- LB7 ----------------------------------------------------------------------- LB79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1215 View this thread: http://www.excelforum.com/showthread.php?threadid=26723 Try =ROUNDDOWN(B19*c19...

Copy a sheet contained formulas from one workbook to another and remove file links
Hello, in our company we use a special excel file with more sheets. Every user copies first this file from the server to his own computer and then he/she can customize the workbook. Usually the user only makes his own new sheets where there are formulas which contain cells from the original sheets. Occasionally a new release of the original excel file appears and the user must make a new copy of this file. Commonly the user wants to have in the new file all of his own created sheets from the first workbook. If he normally copies the sheets from the first workbook to the new one, the f...

Formula request
I need to find a formula that will tell me the frequency of a character appearing within a range of text. For instance, A1 has JUICY and A2 has JUMBO. If I want to find the number of times J appears, it's 2 times Thanks you. One way: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"J",""))) (watch out =substitute is case sensitive, so it won't count lower case j's. If you want to count both j's and J's: =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"J",""))) Joe wrote: > > I need to find a formula that will te...

Rounding Formula
Does anyone know how to build a formula that rounds numbers to the nearest $.50? For example: If the number was $42.26 it would round to $42.50, but if it were $42.24 it would round to $42.00. Denise, =ROUND(C7*1/50,2)*50 Or if you have the analysis toolpack installed (Tools -> Add-ins) =MROUND(C7,0.5) Dan E "Denise" <dhill@pressdemocrat.com> wrote in message news:1e00601c38908$9ed72770$a601280a@phx.gbl... > Does anyone know how to build a formula that rounds > numbers to the nearest $.50? For example: If the number > was $42.26 it would round to $42.50, bu...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

VBA Excel: Move rows which match a certain feature between tabs
Hi, I have a hughe matrix of numbers. Such matrix has headers in its columns (first row). Say that headers are A, B and C (). What I want to do with a VBA script is to look into the rows to check if what of them have the value "30" in the column header. In such case, I would like to move the row from that tab to another one called, e.g., "Tab_30". An important point is that I need that the row in the original matrix has to be deleted. I do not like blank rows in the original matrix nor the new matrix in the tab "Tab_30". I am not an expert in Excel. I just rec...

Checkbook Formula
Hello, I am trying to set up a checkbook register on my computer with excel. I don't have any experience with excel and formulas and was wondering if someone could help me. I have a column for withdraw (subtraction) and deposits (addition) but in the balance column, I want it to differentiate between the addition and subtraction column is that makes sense. Any help is appreciated. Thank you in advance, J Hatz J, there is one here to look at http://office.microsoft.com/search/results.aspx?Scope=TC&Query=checkbook&CTT=6&Origin=EC010331121033 I also have one set up, if ...

Urgent!!! OWA client
Dear all, Setup a OWA 5.5 Server run on W2003 Server at DMZ zone. OWA Server also the same machine running root standalone CA server On PIX, open port 443 access to OWA server on the Internet 1/ OWA users can logon & read their mails externally. But as soon as they click the Reply button, it closes the screen & bring it back to the previuos (Inbox) screen right away. However the reply function works if they logon behind the firewall. Why? 2/ Another major problem is OWA users can not perform "Change Password" function both internally or externally. Thanks!!! Rgds,seeker01...