Using Sumproduct in VBA with boolean values

Does this have to be done in VBA?  Any time I do something like what you're talking about in a worksheet, I always just use a conditional sum that takes array parameters.  For example, if the range A1:A20 holds the day, B1:B20 holds the weather description, and C1:C20 holds the attendance that day, the following formulat in the 21 row would achieve the sum you're looking for:

=SUM(IF(A1:A20&B1:B20="Wednesdaysunny",C1:C20,0))

Enter this formula as an ARRAY FORMULA by typing it in and pressing Ctrl+Shift+Enter.  You will know it was entered properly if the formula is enclosed within curly braces {} in the formula display bar.  Note that you can replace the hardcoded "Wednesdaysunny" part with a concatenation of other cells.

Thank you for the suggestion, but yes, this has to be done in VBA.  

The cell in question may hold any number of values, depending on a case statement in a UDF, and the multi-conditional sum is just one possible case of six, and even then it is merely one component of the relevant calculation.  For now, I'm looping through the range, but I resent writing so much code when there may be a one-liner available.

remarkl,
I got SUMPRODUCT using Boolean values to work in VBA using the Evaluate method as shown in https://www.experts-exchange.com/questions/20745292/Passing-a-named-range-to-SUMPRODUCT-inside-VBA-macro-function.html 
Brad

Such patience!

Before I asked this question, I got as far as

x=[=

and said to myself "Crap, you're gonna have to use "evaluate" and string together one of those horrific agglomerations of XL references and ampersands and vba stuff.  Maybe if I just use the VBA versions of the references, I can use application.worksheetfunction(sumproduct..."

For present purposes, the loop I wrote works well enough.  I was really hoping for an explanation in VB-ese of why application.worksheetfunction would throw a "type mismatch" error in these circumstances, with whatever light such an explanation might shed on the relationship between XL worksheet datatypes and VBA datatypes.  

Brad,

Thanks for the research, for which I'm happy to give you the points.

When I first tried using the application.sumproduct((a=b)*(c=d)*values) construct, I noticed that the VBA hints treated the entire expression as "argument1," so I even tried

application.sumproduct((a=b)*(c=d)*values,1)

but I got type mismatches.  Still, I agree that you need the commas.

Also, if you add to your Sub

dim Z as variant
z=array(True,True,True,True)

you can do

    MsgBox Application.SumProduct(X, Z)

but it returns 0.  If you then add this function:

Function v(b As Boolean)
If b Then v = 1 Else v = 0
End Function

and define
z=array(v(True),v(True),v(True),v(True))

Application.SumProduct(X, Z)

returns 8,

and if you change the last Z value to v(false) or v(3=4),  you get 4, the correct sum.  But you can't do

Application.SumProduct(X, v(Z)) where Z is an array of booleans.  That creates a "byref argument" error  if b is declared as boolean and a type mismatch error if b is declared as a variant.

From all this, I conclude that Excel tries intelligently to build an array out of the expressions in the sumproduct function, but VBA merely processes fully-formed arrays.  Which suggests that the intelligence that processes expressions into arguments in Excel operates independently of the functions themselves and is not available to the application.worksheetfunction engine.  

Just a followup think and do exercise-

Put some 1-digit numbers (include some "2"s) in Column A and some other numbers alongside in Column B.  Name the used part of column A "mydata" and the used part of Column B "myvalues".  Then try this in the immediate window...

a=[=if(mydata=2,1,0)]
b=[=myvalues]
? application.sumproduct(a,b)

or, in other words,

vSunny=[=if(weather="Sunny",1,0)]
vWednesday=[=if(myday="Wednesday",1,0)]
vAttendance=range("Attendance")
totalattendance=application.sumproduct(vSunny,vWednesday,vAttendance)

which works!


LJK

Hi remarkl
Can you have a look at
https://www.experts-exchange.com/questions/20897223/Using-Worksheetfunctions-in-VBA-with-named-ranges-array-formulas.html

Larry,
The N function will convert Boolean to 1 and 0--and it can accept arrays as input. Also worth noting: you don't need an = inside the evaluate function. The following four expressions are all equivalent (and return an array of 1 and 0):

vSunny= [=IF(weather="Sunny",1,0)]
vSunny= [IF(weather="Sunny",1,0)]    
vSunny= [IF(weather="Sunny",1,)]
vSunny= [N(weather="Sunny")]

Putting it all together, here are two different ways of building the SUMPRODUCT formula in VBA:

vSunny= [N(weather="Sunny")]
vWednesday= [N(myday="Wednesday")]
vAttendance= Range("Attendance")
totalattendance= Application.SumProduct(vSunny,vWednesday,vAttendance)

totalattendance= Application.SumProduct([N(weather="Sunny")],[N(myday="Wednesday")],Range("Attendance"))

Brad

Thanks.  I like the N version.

I could swear that bracketed expressions without = failed the first time I tried them, but they obviously work.  I've got a lot of code to clean up, because I use named constants in Xl along with named lookups that convert my dropdown choices to numbers, so, for example, a typical line of code in my macros might be:

if [=luTransactionType]=[=ttSale]

I guess I can search for [=  and replace them all with [.  It'll sure look better.

LJK

Larry,
In a later question, I found that you could multiply by 1 instead of using the N function. This is useful if using range variables rather than named ranges.

Suppose you are looking for a VBA method of calculating a result similar to the worksheet formula:
SUMPRODUCT((D5:D1000=Q28)*(J5:J1000<0))

In the preceding comments, the N function was used inside an Evaluate block to convert the True/False results into 1 and 0. This works fine if you are using named ranges or cell addresses inside the Evaluate block. But if you are using range variables, then multiplying by 1 works better.

Function TestSumproduct(rg1 As Range, crit1, rg2 As Range, crit2)
'Note the use of "=" in the first Boolean expression but not the second. This is because crit2 contains a "<"
'If comparing to a text string, then remember to add the double quotes surrounding it:  & "=""" & crit1 & """)"
TestSumproduct = Application.SumProduct(Application.Evaluate("1*(" & rg1.Address(external:=True) & "=" & crit1 & ")"), Application.Evaluate("1*(" & rg2.Address(external:=True) & crit2 & ")"))
End Function

Call this function with the following worksheet formula:
=TestSumproduct((D5:D1000,Q28,J5:J1000,"<0")

Note the use of a comma to separate the parameters in the Application.Sumproduct. Also note the multiplication by 1 to convert each Boolean expression into an array of 1 and 0.

You may find it convenient to store the Boolean expressions in variant arrays. This makes the final Sumproduct formula much neater in appearance.

Function TestSumproduct(rg1 As Range, crit1, rg2 As Range, crit2)
Dim var1 As Variant, var2 As Variant
var1 = Application.Evaluate("1*(" & rg1.Address(external:=True) & "=" & crit1 & ")")
var2 = Application.Evaluate("1*(" & rg2.Address(external:=True) & crit2 & ")")
TestSumproduct = Application.SumProduct(var1, var2)
End Function

Brad

Brad -

Merry Xmas!

As my absence must indicate, I have not had any interest in EE for months, and this particular question no longer disturbs my sleep.  But since you took the trouble to post, let me at least thank you for thinking of me.

Hope you're well.  Good luck.  If you can make this question go away, feel free to do so.

Larry.

Hello Brad,

Just posted a question in this similar issue:
https://www.experts-exchange.com/questions/27795634/Sum-Product-Consideration-How-To-VBA.html

...if you are interested