VlookupAllSum=SUMPRODUCT

Spielmann

Board Regular
Joined
Nov 9, 2008
Messages
105
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 .^.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Seems pointless, but you could use

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

Spielmann

Board Regular
Joined
Nov 9, 2008
Messages
105
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>


:ROFLMAO:Spielmann .^.
 
Last edited:

Spielmann

Board Regular
Joined
Nov 9, 2008
Messages
105
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 .^.:ROFLMAO:
 

Spielmann

Board Regular
Joined
Nov 9, 2008
Messages
105

ADVERTISEMENT

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?

:ROFLMAO: Spielmann .^.
 

Gettingbetter

Well-known Member
Joined
Oct 12, 2004
Messages
602
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

Board Regular
Joined
Nov 9, 2008
Messages
105

ADVERTISEMENT

Hi,
Gettingbetter

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

Spielmann .^.
 

Spielmann

Board Regular
Joined
Nov 9, 2008
Messages
105
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

Well-known Member
Joined
Oct 12, 2004
Messages
602
Not really sure why you would want this

But here you go anyway

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,666
Messages
5,597,456
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top