Simple example
The following
VBA code inserts a few values into the first and second column and then inserts the
sumproduct formula into the lower right adjacent cell to calculate the sum of the numbers in the same row:
option explicit
sub main() ' {
'
' Clear active sheet's data:
'
activeSheet.usedRange.clearContents
cells(1,1) = 5 : cells(1,2) = 3
cells(2,1) = 7 : cells(2,2) = 2
cells(3,1) = 4 : cells(3,2) = 6
cells(4,1) = 1 : cells(4,2) = 9
cells(5,3).formulaR1C1 = "= sumproduct( r1c1:r4c1 , r1c2:r4c2 )"
end sub ' }
The result is 62 = 5*3 + 7*2 + 4*6 + 1*9:
Finding equal values
The following example tries to demonstrate how sumproduct can be used to sum the values in the third coulumn where the values in the first and second column are equal.
In the inserted formula, the first array is constructed by comparing the values of the first and second column which returns a
boolean value. The
-- (minus minus) in the formula is then used to convert this boolean value into
0 or
1.
option explicit
sub main() ' {
'
' Clear active sheet's data:
'
activeSheet.usedRange.clearContents
'
' Fill some data
'
cells(1,1) = "foo" : cells(1,2) = "bar": cells(1,3) = 1
cells(2,1) = "bar" : cells(2,2) = "baz": cells(2,3) = 4
cells(3,1) = "foo" : cells(3,2) = "foo": cells(3,3) = 2
cells(4,1) = "baz" : cells(4,2) = "foo": cells(4,3) = 17
cells(5,1) = "baz" : cells(5,2) = "bar": cells(5,3) = 8
cells(6,1) = "bar" : cells(6,2) = "foo": cells(6,3) = 18
cells(7,1) = "bar" : cells(7,2) = "bar": cells(7,3) = 22
cells(8,1) = "bar" : cells(8,2) = "baz": cells(8,3) = 5
'
' Calculate sum of numbers where value in first and second columns are equal:
'
cells(9,3).formulaR1C1 = "= sumproduct( -- ( r1c1:r8c1 = r1c2:r8c2 ), r1c3:r8c3 )"
end sub ' }
The formula returns 24 which is the sum of 2 (3rd row) and 22 (7th row):
