SUMPRODUCT working in worksheet but not in VBA - please help!

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Aug 26, 2010

• #2

Missing a single quote and the double quotes need doubling up:

t = Evaluate("=SUMPRODUCT(('Sheet 1'!$W$2:$W$15994>=B19)*('Sheet 1'!$W$2:$W$15994<=B20)*('Sheet 1'!$V$2:$V$15994=""M - filed as complete""))")

 

Upvote 0

N

nick_clarke

New Member

Joined

Aug 26, 2010

Messages

27

• Aug 26, 2010

• #3

Thanks Andrew, that worked and I have used it as an example to amend my other vba sumproduct forumulas.

I'm still struggling on the ones that involve MONTH and YEAR criteria. The functions work in the spreadhseet but again I am receiving a type 13 mismatch error. The code is as follows:

C = Evaluate("=SUMPRODUCT(--(MONTH('Sheet 1'!$F$2:$F$16000)=MONTH(TODAY()))*(--(YEAR('Sheet1'!$F$2:$F$16000)=YEAR(TODAY())))")

I don't believe that I am missing any quotation marks and that the correct number of brackets have been used, however I appreciate that something must be wrong.

Hope you can help ease my headache

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Aug 26, 2010

• #4

That's:

C = Evaluate("=SUMPRODUCT((MONTH('Sheet 1'!$F$2:$F$16000)=MONTH(TODAY()))*(YEAR('Sheet 1'!$F$2:$F$16000)=YEAR(TODAY())))")

 

Upvote 0

N

nick_clarke

New Member

Joined

Aug 26, 2010

Messages

27

• Aug 26, 2010

• #5

Thanks again for your assistance the quick reply.

I'm unfortunately still getting a type 13 mismatch error

I note that you removed the proceeding '--' from both the MONTH and YEAR criterias - could this be related to the error or is it related to brackets and quotation marks?

Thanks

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Aug 26, 2010

• #6

This worked for me:

Code:

Sub Test()
    Dim C As Integer
    C = Evaluate("=SUMPRODUCT((MONTH('Sheet 1'!$F$2:$F$16000)=MONTH(TODAY()))*(YEAR('Sheet 1'!$F$2:$F$16000)=YEAR(TODAY())))")
    MsgBox C
End Sub

 

Upvote 0

N

nick_clarke

New Member

Joined

Aug 26, 2010

Messages

27

• Aug 26, 2010

• #7

Thanks again for the quick reply.

Unfortunately, it is still not working. My code is as follows:

Dim C As Integer

C = Evaluate("=SUMPRODUCT((MONTH('Test Sheet'!$F$2:$F$16000)=MONTH(TODAY()))*(YEAR('Test Sheet'!$F$2:$F$16000)=YEAR(TODAY())))")

txtcrmtd.Value = C

Produces a type 13 mismatch error. Please note that I've changed my sheet name to Test Sheet, could this be the reason for the error?

Hope you can help.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Aug 26, 2010

• #8

This worked perfectly for me with a TextBox named txtcrmtd on a UserForm:

Code:

Private Sub CommandButton1_Click()
    Dim C As Integer
    C = Evaluate("=SUMPRODUCT((MONTH('Test Sheet'!$F$2:$F$16000)=MONTH(TODAY()))*(YEAR('Test Sheet'!$F$2:$F$16000)=YEAR(TODAY())))")
    txtcrmtd.Value = C
End Sub

What does this formula return when entered on a worksheet?

=SUMPRODUCT((MONTH('Test Sheet'!$F$2:$F$16000)=MONTH(TODAY()))*(YEAR('Test Sheet'!$F$2:$F$16000)=YEAR(TODAY())))

 

Upvote 0

N

nick_clarke

New Member

Joined

Aug 26, 2010

Messages

27

• Aug 26, 2010

• #9

it returns the number of cells in a column where the date is the same month as the current month i.e 20/08/10 is counted but 20/07/10 is not.

my data in column F is as follows:

23/08/2010
22/08/2010
20/08/2010
27/07/2010

within the worksheet the sumproduct returns value of 3 which is correct as 3 of the cells have date where the month matches the current month of august.

hope this helps.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Aug 26, 2010

• #10

With that data the code I posted put 3 in the TextBox.

 

Upvote 0