vba sumRange as per Criteria

[Mallesh23]

Hi Team,

I want to sum scores of few playear as per Criteria List.
Column A and B are my Data.Criteria list is in Column E. List may Increase.

Is there any direct excel formula? to get the same

looking in excel formula /Dictionary/loop

Name	Score			Criteria List		Expected sum
Dhoni	10642​			Sehwag
Virat	11498​			Dhoni
Rohit	10857​				Total Scores Made by Sachin and Sehwag	22424​
Sachin	12657​
Sehwag	11782​

My attempted code which is not working. getting stuck second loop.

Option Explicit
Sub Sum_Range_as_per_Criteria()

    Dim i As Long, Arr_Cr As Variant
    Dim int_total As Long
    
    'Criteria Value
    Arr_Cr = WorksheetFunction.Transpose(Range("E2:E3").Value)
    
    For i = 2 To Range("A1000").End(xlUp).Row
        If IsNumeric(WorksheetFunction.Match(Cells(i, 1).Value, Arr_Cr, 0)) Then
            int_total = int_total + Cells(i, 2).Value
        End If
    
    Next i


End Sub

Thanks
mg

 

[alansidman]

Maybe:

Option Explicit

Sub Scores()
    Dim i As Long, j As Long, lr As Long, lrE As Long
    Dim x As Integer
    Application.ScreenUpdating = False
    x = 0
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lrE = Range("E" & Rows.Count).End(xlUp).Row
    For i = 3 To lr
        For j = 3 To lrE
            If Range("A" & i) = Range("E" & j) Then
                x = x + Range("B" & i).Value2
            End If
        Next j
    Next i
    Range("G5") = x
    Application.ScreenUpdating = True
    MsgBox "Completed"

End Sub

 

[Mallesh23]

Hi AlanSidman,

Superb ! Thank you so much for your help, got as expected result. ?

is there excel built in formula to get same result.

also how to get same result via dictionary , I am trying to learn dictionary here.


Thanks
mg

 

[Fluff]

Duplicate vba Dictionary - Sum ranges as per criteria

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).