[Solved] Need to Sum multiple columns

[PhilOSU2004]

I need to sum or average multiple date columns. I can pull an individual cell via index match which I am already doing, but not sure how to pull multiple data via index and match.

For example need to lookup the last 12 months of data based on the following:
- Employee
- Data Type - (example: Estimator Backlog)
- and prior to a date - So in this case the 12 periods prior to 5/1/23


Below is my data table:




Output sheet with parameters:

 

[Rubayed Razib]

Greetings PhilOSU2004,
I understand the first two criteria you mentioned, but could not understand the third one. It would be much easier if you had provided us with the source file with some examples.
Although I present a sample VBA code to a sample dataset in order to solve the problem.In my sample dataset, we have similar column value as you have given. just restricted the date to only One year.
=SUM(INDEX(C2:N26, MATCH(A1&O1, A2:A26&B2:B26, 0), 0):INDEX(C2:N26, MATCH(A1&O1, A2:A26&B2:B26, 0), COLUMNS(C2:N26)))

we basically get the summation of monthly sales based on the two criteria mentioned in cells A1 and O1.
we also presented a VBA macro to assist you in this case,

Sub LookupAndSum()
    Dim lookupRange As Range
    Dim resultCell As Range
    Dim lookupValue As Range
    Dim matchValue As Range
    Dim sumValue As Double
    Dim foundCell As Range
    Set lookupValue = Range("A1")
    Set lookupRange = Range("A2:N26")
    Set resultCell = Range("P2")
    sumValue = 0
    For Each foundCell In lookupRange.Columns(1).Cells
        If foundCell.Value = lookupValue.Value Then
            Set matchValue = foundCell.Offset(0, 1)
            If matchValue.Value = Range("O1").Value Then
                sumValue = WorksheetFunction.Sum(foundCell.Offset(0, 2).Resize(1, lookupRange.Columns.Count - 2))
                resultCell.Value = sumValue
                Exit Sub
            End If
        End If
    Next foundCell
    resultCell.ClearContents
End Sub

This code actually based on this worksheet. if you want more flexibility, you better send your workbook. Aftre running this code we will see that the total sale value throughout the year is now present in the call P1.