CODEWITHSUNDEEP
vbaexcel
AjayR
AjayR

Reputation: 4179

VBA Excel, VLookup for multiple values count

I have One excel file.

 EmployeeName                   Expenses
 Ajay                                      2000
 Vijay                                     1000
 Ajay                                      1500
 Vijay                                     500

I want to count the number of expenses for each employee. I can get the first occurance of the Expenses of the given employee with Vlookup like this

sExpense = XLApplication.VLookup("Ajay", Range("A1:B99"), 2, False)

But how Can I find the sum of all expenses from a given Employee.

Upvotes: 1

Views: 2971

Answers (2)

ray
ray

Reputation: 8699

Option Explicit

Public Sub test()
  Dim sExpense As String
  Dim range1 As Range
  Dim sumRange As Range

  Set range1 = Range("A2:A5")
  Set sumRange = Range("b2:b5")

  sExpense = Me.Application.WorksheetFunction.SumIf(range1, "Ajay", sumRange)

End Sub

Upvotes: 2

lamwaiman1988
lamwaiman1988

Reputation: 3742

You can use the SUMIF function provided by excel:

For example 

C6=SUMIF(A:A,"Vijay",B:B)

It look through the range in A:A (column A) for any cell match "Ajay" and sum their total in Column B

enter image description here

Upvotes: 5

Related Questions