Use Sumproduct function in VBA for sum of last date value

[Ashish Bhalara]

Hello experts,

​​

I getting error of Type Mismatch in below code of Sumproduct function.

​Kindly check what going wrong in this. I share example file in attachement.

Sub ConditionalSum()

Dim Rng1 As Range, Rng2 As Range

Dim maxDt As Date

Dim ws As Worksheet

Dim ttlPay As Double

Set ws = Worksheets("Sheet1")

Set Rng1 = ws.Range("D4:D11")

Set Rng2 = ws.Range("E4:E11")

maxDt = WorksheetFunction.Max(ws.Range("D4:D11"))

ttlPay = Application.Evaluate("SumProduct((" & Rng1 & " = " & maxDt & ") * (" & Rng2 & "))")

'ws.Range("A1") = Application.Evaluate "=SumProduct((ws.Range("D4:D11") = " & maxDt & ") * (ws.Range("E4:E11")))"

ws.Range("A1").Value = ttlPay

End Sub

​Regards

Ashish Bhalara

[vabs]

is it resolved?

[Ashish Bhalara]

Yes, It's resolve now.

I use SumIf instead of SumProduct as below.

ttlPay = Application.WorksheetFunction.SumIf(Rng1, maxDt, Rng2)

Regards

Ashish Bhalara

--
www.ExcelVbaLab.com
---
You received this message because you are subscribed to the Google Groups "Excel VBA Lab - An Excel VBA Macro help Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ExcelVbaLab...@googlegroups.com.
To post to this group, send email to Excel...@googlegroups.com.
Visit this group at https://groups.google.com/group/ExcelVbaLab.
To view this discussion on the web visit https://groups.google.com/d/msgid/ExcelVbaLab/6a2e2fa2-d5f0-4cbe-880b-919b92de12c0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.