VlookupAllSum=SUMPRODUCT

[Spielmann]

Hi Colleagues,

I behind developed to a time a function of Addition with an only conditional criterion.
I would like to extend at least for three criteria, this function I function accurately as the function SUMPRODUCT alone that done in VBA.
Somebody could help me to develop?

Debtor

Function VlookupAllSum(name As String, IntervalSearches As Range, IntervalReturn As Range) As Variant ' as integer para valores até 32.767
Dim Valor, Nome
Dim lin, col As Integer
Dim Total
Application.Volatile
lin = 1
col = lin
For Each Nome In IntervalSearches
If Nome = name Then
Valor = IntervalReturn(lin, col)
Total = Total + Valor
VlookupAllSum = Total
End If
lin = lin + 1
Next Nome
VlookupAllSum = Total
End Function

Ex:=VlookupAllSum(A1,A1:A6,C1:C6)=33
=SUMPRODUCT(--(A1:A6=A1),(--(C1:C6)))

=VlookupAllSum(A4,A1:A6,C1:C6)=54
=SUMPRODUCT(--(A1:A6=A4),(--(C1:C6)))
A----------------B-------C
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64 x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>19</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR></TBODY></TABLE>


Spielmann .^.

 

[xld]

Seems pointless, but you could use

Function VLookupAllSum(Lookup, LookAt As Range, SumRng As Range)
VLookupAllSum = Application.SumIf(LookAt, Lookup, SumRng)
End Function

 

[Spielmann]

Xld,
I liked the simplification of what I made, but necessary to at least extend the number of criteria for 3.
Type:

=SUMPRODUCT(--(A1:A6=A1),(--(B1:B6=3)),(--(C1:C6)))
=SUMPRODUCT(--(A1:A6=A1),(--(B1:B6=3)),(--(C1:C6<15)))
=SUMPRODUCT(--(A1:A6=A4),(--(B1:B6=3)),(--(C1:C6>12)),(--(D1:D6)))=10

A----------------B-------C---------D
<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64 x:num>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=64 x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>11</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>12</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>15</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>19</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>55</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>20</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR></TBODY></TABLE>


Spielmann .^.

 

[Spielmann]

Xld,

I need to at least extend the number of criteria for three beyond he creaks it of addition.
You it could help me please in this function?

Spielmann .^.

 

[Spielmann]

Hi,
I am trying to develop a function in VBA that functions accurately as function SUMPRODUCT (), but necessary that he at least takes care of 3 criteria beyond the addition column.

Can you help me please?

Spielmann .^.

 

[Gettingbetter]

You can have multiple criteria with sumproduct

to count D equal to 10
=SUMPRODUCT(--(A1:A6=A1),--(B1:B6=3),--(C1:C6>12),--(D1:D6=10))

to sum column D
=SUMPRODUCT(--(A1:A6=A1),--(B1:B6=3),--(C1:C6>12),(D1:D6))


Cheers
GB

 

[Spielmann]

Hi,
Gettingbetter

I know that he is possible using SUMPRODUCT, but necessary to develop in VBA.

Spielmann .^.

 

[Gettingbetter]

Sorry mate,

check out NateOs responce in this thread
http://www.mrexcel.com/forum/showthread.php?t=65622

Taken from thread

Sub TestOfSumProduct2()
Dim vResult As Variant
vResult = _
[SUMPRODUCT(($A$1:$A$265="TS")*($C$1:$C$265="Net Interest - Interco")*($E$1:$E$265="Y"))]
MsgBox vResult
End Sub

Cheers
GB

 

[Spielmann]

Gettingbetter
I need to only develop in format of Function defined for the user, as I made at the beginning, that extending I number it of criteria as my VlookupAllSum function

Spielmann

 

[Gettingbetter]

Not really sure why you would want this

But here you go anyway

Function VlookupAllSum(IntervalReturn As Range, name As Variant, IntervalSearches As Range, Optional name2 As Variant, Optional IntervalSearches2 As Variant, Optional name3 As Variant, Optional IntervalSearches3 As Variant)
  Dim rngeA As Range, rngeB As Range, rngeC As Range
  Dim vResult As Variant
  If IsMissing(IntervalSearches2) Then
    If Not Application.WorksheetFunction.IsNumber(name) Then name = """" & name & """"
    Set rngeA = IntervalSearches
    vResult = Evaluate("SumProduct(--(" & rngeA.Address & " = " & name & ")," & "(" & IntervalReturn.Address & "))")
  ElseIf IsMissing(IntervalSearches3) Then
    If Not Application.WorksheetFunction.IsNumber(name) Then name = """" & name & """"
    If Not Application.WorksheetFunction.IsNumber(name2) Then name2 = """" & name2 & """"
    Set rngeA = IntervalSearches
    Set rngeB = IntervalSearches2
    vResult = Evaluate("SumProduct(--(" & rngeA.Address & " = " & name & "),--(" & rngeB.Address & " = " & name2 & ")," & "(" & IntervalReturn.Address & "))")
    MsgBox ("SumProduct(--(" & rngeA.Address & " = " & name & "),--(" & rngeB.Address & " = " & name2 & ")," & "(" & IntervalReturn.Address & "))")
  Else
    If Not Application.WorksheetFunction.IsNumber(name) Then name = """" & name & """"
    If Not Application.WorksheetFunction.IsNumber(name2) Then name2 = """" & name2 & """"
    If Not Application.WorksheetFunction.IsNumber(name3) Then name3 = """" & name3 & """"
    Set rngeA = IntervalSearches
    Set rngeB = IntervalSearches2
    Set rngeC = IntervalSearches3
    vResult = Evaluate("SumProduct(--(" & rngeA.Address & " = " & name & "),--(" & rngeB.Address & " = " & name2 & "),--(" & rngeC.Address & " = " & name3 & ")," & "(" & IntervalReturn.Address & "))")
  End If
  VlookupAllSum = vResult
End Function

where

With 2 criteria
=VlookupAllSum(C1:C6,A1,A1:A6,B1,B1:B6)
=SUMPRODUCT(--(A1:A6=A1),--(B1:B6=B1),(C1:C6))

is the equivalent of

With 3 criteria
=VlookupAllSum(C1:C6,A1,A1:A6,B1,B1:B6,D1,D1:D6)
=SUMPRODUCT(--(A1:A6=A1),--(B1:B6=B1),--(D1:D6=D1),(C1:C6))

Cheers
GB