Sumproduct calculation vba

[Av8tordude]

How can I use this formula in vba...

=SUMPRODUCT(--(TEXT($D$15:$D$1001,"mmmmyyyy")=$S23&T$14),$P$15:$P$1001)

Thank you kindly

 

[James006]

Hello,

When you say ' use ' this formula ...

What is your actual objective :

1. The macro should insert this formula in your worksheet

2. The macro should work out the result of your formula ... and then insert this result in your worksheet

HTH

 

[Av8tordude]

I want to use vba instead of using a formula

 

[James006]

You can use the Evaluate function

 

[Av8tordude]

Ok...I've tried really hard to figure out how use "Evaluate" function in conjunction with userform combo boxes, but I fried too many brain cells and feel defeated. I have "cbMon" and "cbYr" combo boxes in a userform that I will used to select the Month & Year. In my original formula: =SUMPRODUCT(--(TEXT($D$15:$D$1001,"mmmmyyyy")=$S23&T$14),$P$15:$P$1001), I want to replace $S23 & T$14 with the "cbMon" & cbYr. My failed attempt to reconcile this problem is below... Can some one assist please.

Thank you kindly

CCI.Caption = Evaluate("SUMPRODUCT(--(TEXT(D15:D1001,""mmmmyyyy"")= & Month & cbYr & ,P15:P1001")

 

[Av8tordude]

anyone?

 

[James006]

What is the result produced by your formula ... when used in your worksheet ...

You are replacing S23 by CbMon and T14 by CbYr ... are they both comboboxes ... ? ( holding Text ...)

 

[Av8tordude]

What is the result produced by your formula ... when used in your worksheet ...

You are replacing S23 by CbMon and T14 by CbYr ... are they both comboboxes ... ? ( holding Text ...)

basically, it totals the amounts for all the entries within the month & year selected.

 

[Av8tordude]

What is the result produced by your formula ... when used in your worksheet ...

You are replacing S23 by CbMon and T14 by CbYr ... are they both comboboxes ... ? ( holding Text ...)

cbMon will have the Months (i.e. January, February, etc.) and cbYr with have 2018, 2019, 2020, etc

 

[James006]

Would you mind answering the second question :

You are replacing S23 by CbMon and T14 by CbYr ... are they both comboboxes ... ? ( holding Text ...)