VBA to search for different valuse in entire workbook depending on texts

Hi everyone
I have a workbook with differnt sheets and different data and values and a master sheet.
I need to search for all different values in the entire workbook for available worksheets and future sheets also, after finding the values according to the criteria in master sheet i want the values all to be added in new column near each criteria. I was using before sumif formula but now it is very hard since i am having over 50 sheets and over 300 different criteria. Can you please help me with VBA code for that issue.
Attached is an exmple of what I mean.
Thanks for the help

Try

Code:

---

Sub test()
    Dim a, b, i&, wsM As Worksheet, ws As Worksheet, r As Range
    Set wsM = Sheets("Master sheet ")
    a = wsM.Range("b1", wsM.Range("b" & Rows.Count).End(xlUp)).Resize(, 2).Value2
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 2 To UBound(a, 1)
        For Each ws In Worksheets
            If Not ws Is wsM Then
                Set r = Intersect(ws.UsedRange, ws.Columns("a:g"))
                b(i, 1) = b(i, 1) + ws.Evaluate("sumif(" & r.Columns("b").Address & _
                    ",""" & a(i, 1) & """," & r.Columns("g").Address & ")")
            End If
        Next
    Next
    wsM.[e1].Resize(UBound(b, 1)) = b
End Sub

---

Many thanks for the help it works after preparing the correct file for that purpose.