Sumproduct with criteria in VBA?

[SabCie]

Hi,

I would like to make a sumproduct with a criteria while only using VBA, Is this something possible?

So to make this clearer, I have a document with 3 columns.

Column A = Category
Column B = Amount
Column C = Quantity

So I would like a VBA function that will give me the total amount (Amount*Quantity) for a specific category...

Help is really appreciated.

Thanks

 

[GlennUK]

Hi, you have to use Evaluate to run the worksheet function. See this example:

Excel Workbook
	A	B	C	D	E	F
1	Category	Amount	Quantity	Check column	Check result
2	Food	5.02	4	20.08	Category	Food
3	Drink	19.92	8	159.36	Sum	675.62
4	Dancing	19.46	15	291.9
5	Food	23.25	5	116.25
6	Drink	8.94	7	62.58
7	Dancing	18.26	8	146.08
8	Food	25.25	16	404
9	Drink	5.51	10	55.1
10	Dancing	20.32	11	223.52
11	Food	23.15	3	69.45
12	Drink	12.87	10	128.7
13	Dancing	17.62	8	140.96
14	Food	8.23	8	65.84
Sheet24

Well, this VBA displays the same result ( 675.62 ), by using SUMPRODUCT:

Sub doSUMPinsheet()
myres = Evaluate("SUMPRODUCT(--(A2:A14= ""Food"")*(B2:B14)*(C2:C14))")
MsgBox myres
End Sub

 

[GlennUK]

By the way, you could coerce your text test into numbers in an array first, and then use sumproduct normally, like this:

Sub doSUMPinsheetCoerceTextTest()

myarray = Evaluate("=--(A2:A14=""Food"")")

myres = Application.SumProduct(myarray, Range("B2:B14"), Range("C2:C14"))

MsgBox myres
End Sub

 

[SabCie]

GlennUK, thank you so much.

Exactly what I needed!!

 

[slachance]

hi!

It's possible to put a variable in this code?

myrangeisvariable="A2:A14"
mynameisvariable="FOOD"

myarray = Evaluate("=--(myrangeisvariable=FOOD)")

I want to know how the right code for that.

Thanks for your help and sorry for my horrible english