VBA Sum If With Multiple Criteria

Question

I wonder whether someone may be able to help me please. I've been trying, for about a week now to put together a sheet which encompasses a 'SumIf' Formula based on a multiple criteria. I've been able to do this, manually entering the formula with the desired results in a cell range. The problem I have is really two fold. - The formula I used was an array formula, but the skill level of some of my users is perhaps not as good as I would hope, so I know they won't be able to cope adding the formula themselves, so I looked at the option of using VB code, - The problem with this is, I know you cannot use an array formula in VB, so I'm a little unsure how to proceed. To perhaps illustrate this a little better, I've attached a file here shows what I'm trying to achieve. - On the sheet "Slide 5", I'm would like to extract the results in the columns shaded orange, And for each cell I would like to perform the following 'SumIf': - Sum the figure in the named range "Actuals", - If the value in…

Accepted Answer

If you wanted to run a Sub to populate the orange fields then my code would fill in the data for you

```
Option Explicit

Sub GetSolution()

' declaring a variable of Worksheet type for easier reference
    ' instead of saying: Sheets("All Data") you can use a shorter
    ' variable name: "data"
    Dim data As Worksheet
    Set data = Sheets("All Data")

Dim result As Worksheet
    Set result = Sheets("Slide 5")

' these two variables hold the SUM for both analyst types
    ' in the loops flow
    Dim seniorAnalystSum As Double
    Dim analystSum As Double

' using resource and projectLob Range objects for quick reference to cells
    ' in both spreadsheets
    ' so again, instead of saying data.Range("A" & i) etc
    ' simple and short way is "resource" and/or "projectLob"
    Dim resource As Range
    Dim projectLob As Range

' for each cell in between K8:K16 on the result/ Sheets("Slide 5")
    ' pick up one cell at a time
    For Each resource In result.Range("K8:K16")
        ' and go over all cells between B5 and last cell in Data sheet
        For Each projectLob In data.Range("B5:B" & data.Range("B" & Rows.Count).End(xlUp).Row)
            If projectLob = resource Then
                ' compare year and month between the matching cells
                If (Month(projectLob.Offset(0, 8)) = Month(result.Range("B3"))) And _
                (Year(projectLob.Offset(0, 8)) = Year(result.Range("B3"))) Then
                    ' compare to the row of the table that holds the type of analyst
                    If projectLob.Offset(0, 7) = result.Range("L6") Then
                        'C&R Senior Analyst
                        seniorAnalystSum = seniorAnalystSum + projectLob.Offset(0, 12)
                    ElseIf projectLob.Offset(0, 7) = result.Range("N6") Then
                        'C&R Analyst
                        analystSum = analystSum + projectLob.Offset(0, 12)
                    End If
                End If
            End If
        Next
        ' Assigns the sum for the senior analyst
        resource.Offset(0, 2) = seniorAnalystSum
        ' assigns the sum for normal analyst
        resource.Offset(0, 4) = analystSum
        ' reset both sums for the next loop
        seniorAnalystSum = 0
        analystSum = 0
    Next

End Sub
```

I have used your sample data and populated the results as follows

I hope this is the kind of solution you're looking for :) Awaiting your feedback

VBA Sum If With Multiple Criteria

Solution

Problem