non-sequential matrices

E

Edson

Hi,

I have values in two columns and I want the sum of the product of them. The
problem is: the values in column A are in a non-sequential disposal, as the
sample below:
A B C D
1 10 1
2 2
3 20 3
4 4
5
6 30
7
8
9 40
I tried to use this matrix formula {=SUM(A1;A3;A6;A9*C1:C4)} and the result
was 460 instead of 300.
I cannot realize how to solve this problem. Any ideas?

Thanks in advance
 
B

bplumhoff

Hello,

enter this UDF:
Option Explicit

Function nonempty(r As Range) As Variant
Dim vE As Variant, i As Long
ReDim vR(0 To r.Count) As Variant
i = 0
For Each vE In r
If Not IsEmpty(vE) Then
vR(i) = vE
i = i + 1
End If
Next vE
'ReDim Preserve vR(0 To i - 1) As Variant
nonempty = vR
End Function

Then
=SUMPRODUCT(nonempty(A1:A9),nonempty(C1:C9))
will result in 300.

HTH,
Bernd
PS: If you activate ("de-comment") the commented line then you have to
be sure that the count of non-empty cells in both ranges is identical.
 
E

Edson

Thanks for your contribution, Bernie but I was thinking about a more
comprehensive solution.
It was simple if I had so few values; that is just an example, the real data
is much, much longer, so this kind of solution doesn't apply
 
Top