Adding data from specific years

[Marvo]

Hi, I'm trying to total data from specific year. 2009, 2010 and 2011. If I enter the following
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")
it produces the correct result for 2009
If I then add to the formula
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")+SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010")
it produces the correct result for 2009 & 2010
However if I add the third condition
=SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2009")+SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2010")+SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,"2011")
it no longer produces a result at all AND it also stops the second condition working too.
Any idea why?

I'm sure in all likelihood the formula could be simplified for all three years as it is repeating itself but I don't know how.

Many thanks

 

[Logit]

I was imagining I'd just have 5 buttons, each one to sort the data by the column I needed at that time.

That is very doable. And a more direct approach than what I previously thought would help (which it obviously doesn't). Give me some time to review your comments in Post #18.

Merry Christmas ! (It's Christmas Eve here)

 

[Marvo]

I've worked out a way of separating the data of people who have managed more than once, made the formula much simpler without the need for years. Once I've finished doing that the code required should be very simple.

Don't spend your valuable holiday time working on this, unless you are like me and find the challenge stimulating.

 

[Logit]

Here is the amended code for sorting :

Sub SortByColumnAAscending()
    ' Sort by Column A in ascending order
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    With ws
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        With .Sort
            .SetRange ws.Range("A1:AA" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Sub SortByColumnDDescending()
    ' Sort by Column D in descending order
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    With ws
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        
        With .Sort
            .SetRange ws.Range("A1:AA" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Sub SortByColumnKDescending()
    ' Sort by Column K in descending order
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    With ws
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("K2:K" & .Cells(.Rows.Count, "K").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        
        With .Sort
            .SetRange ws.Range("A1:AA" & ws.Cells(ws.Rows.Count, "K").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Sub SortByColumnLDescending()
    ' Sort by Column L in descending order
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    With ws
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("L2:L" & .Cells(.Rows.Count, "L").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        
        With .Sort
            .SetRange ws.Range("A1:AA" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Sub SortByColumnAADescending()
    ' Sort by Column AA in descending order
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    With ws
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("AA2:AA" & .Cells(.Rows.Count, "AA").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        
        With .Sort
            .SetRange ws.Range("A1:AA" & ws.Cells(ws.Rows.Count, "AA").End(xlUp).Row)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .Apply
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Download workbook : Internxt Drive – Private & Secure Cloud Storage

Any sorts that are performed can be undone by clicking the Column A sort button.

 

[Marvo]

Many thanks.

 

[Logit]

You are welcome. Merry Christmas