Getting totals of each primary key by matching it with another worksheet with a many relationship
You've not provided a fully functional set of code with data, so I can only make untested suggestions as an example for you to incorporate into your application.

Without seeing the data that you are working with, it's impossible to know if a Dictionary is the best approach. Using a Dictionary is a good idea, but only if all of the data in the table is unique. In your code fragment, for example, you are relying on the comparison of two specific cells and are not searching for a value within the entire range of data (which is better suited to a Dictionary).

Using the Index or Match is also reasonable, but remember that these functions directly interact with the worksheet cells. With larger data sets in the Range, this results in slower execution because the Range object must be traversed to access the desired Cells.

Memory-based arrays would be my recommendation for the execution speed improvements you're looking for, with a straight up array-to-array comparison. You can learn more information about using arrays here and here.

Again, you didn't include a complete set of your code for review here, but there are a few observations I can make for improvement.


Always use Option Explicit. (You may be doing this, but we can't see it)
Create intermediate objects/variables to make your code more readable. As an example, you are repeating the full reference for a worksheet as ThisWorkbook.Worksheets("Prod. Qty.") everywhere. 


While it might seem more tedious at first, trust me when I say it will make your code far more readable and easier to maintain if you set it up like this:

Dim prodQtyWS As Worksheet
Dim dashboardWS As Worksheet
Set prodQtyWS = ThisWorkbook.Worksheets("Prod. Qty.")
Set dashboardWS = ThisWorkbook.Worksheets("Dashboard")



Establish your ranges explicitly so the extent of your data is clear.


Again, it's a few extra lines of code, but it will not slow down the execution time. (My example here may be an incorrect reading of your OP, you'll have to make adjustments.)

Dim prodQtyRange As Range
Set prodQtyRange = prodQtyWS.Range("A2").Resize(Total_rows_Prod, 31)


From here on out it's implementing your business logic to update the dashboard data. Here is my example Sub, which again, you have to adapt to your specific application. You will see a significant speed improvement with this method.

Option Explicit

Sub Example(ByVal Total_rows_Prod As Long, ByVal Total_rows_Dash As Long)
    Dim prodQtyWS As Worksheet
    Dim dashboardWS As Worksheet
    Set prodQtyWS = ThisWorkbook.Worksheets("Prod. Qty.")
    Set dashboardWS = ThisWorkbook.Worksheets("Dashboard")

    '--- establish the ranges and copy the data into a memory array
    Dim prodQtyRange As Range
    Dim prodQtyData As Variant
    Set prodQtyRange = prodQtyWS.Range("A2").Resize(Total_rows_Prod, 31)
    prodQtyData = prodQtyRange.Value

    Dim dashboardRange As Range
    Dim dashboardData As Variant
    Set dashboardRange = dashboardWS.Range("A2").Resize(Total_rows_Dash, 31)
    dashboardData = dashboardRange.Value

    Dim i As Long
    Dim j As Long
    For i = 2 To Total_rows_Prod
        '--- calc this factor once, since we're adding the same value
        '    to all the dashboard rows
        Dim prodQtyFactor As Double
        prodQtyFactor = prodQtyData(i, 31) / prodQtyData(i, 4)
        For j = 2 To Total_rows_Dash
            If prodQtyData(i, 5) = dashboardData(i, 1) Then
                dashboardData(j, 4) = dashboardData(j, 4) + prodQtyFactor
            End If
        Next j
    Next i

    '--- copy the updated data back to the dashboard
    dashboardRange.Value = dashboardData

End Sub