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

 

[Marvo]

What I was trying to solve was I have a list of managers and the years they were in control. You could search the entire columns (over 5000) and get results for individuals. However I had three people that did more than one period, hence the SUMIFS. I've reverted back to giving these people a number, so Steve Smith 1, Steve Smith 2, etc but there are times when I need the totals of all the periods they were in charge. I'll get round it, just a shame I couldn't do it in the one formula.

Thanks again for taking an interest.

 

[Logit]

If you can use a VBA approach, I can give you a solution to "but there are times when I need the totals of all the periods they were in charge." What the macro will do
is separate all listings for each employee and paste that data to a tab reserved for them only. That would give you all the information in one location without any other
data. Would that work ?

 

[Marvo]

I have a basic idea of how VBA works, I did have them on this very sheet for a time, kept changing from consecutive year, best to worst in regards points, success, length of time, etc. However one day they stopped working, something about subscript out of range and I didn't know how to repair them. That's the problem isn't it, unless you keep repeating tasks you forget what you've learned.

 

[Logit]

Ifyou are interest I'll need a basic mockup of your workbook with maybe 10 row of example data. Please post it to a download site like DROPBOX.COM or similar.

 

[Marvo]

I've got drop box, my stuff goes to there every time I update the workbook. Not sure how I give you access to it though?

 

[Logit]

Post the link to the file here.

 

[Cubist]

Try:

=SUM(SUMIFS(ALL!$L:$L,ALL!$N:$N,$B17,ALL!$D:$D,{"2009","2010","2011"}))

 

[Marvo]

Dropbox

www.dropbox.com

I hope that works. Its the sheet named managers.

I sort by Column A for a Chronological order
Column D for most matches in charge
Column K for success rate
Column L for Win Ratio
Column AA for best at higher level

All high to low

The names that give me the problems when sorting are Dave Bowen (4 instances), Ian Sampson (3), Clive Walker (2) and Bill Dodgin Jnr (2).

Thanks for this.

 

[Logit]

I'm not certain the following is going to help you ... but here it is anyway.

Option Explicit

Sub CreateUniqueSheets()
    Dim wsSource As Worksheet
    Dim wsNew As Worksheet
    Dim uniqueTerms As Collection
    Dim cell As Range
    Dim term As Variant
    Dim lastRow As Long
    Dim newRow As Long
    Dim headerRow As Range
    
    Set wsSource = ThisWorkbook.Sheets("managers")
    Set uniqueTerms = New Collection
    lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
    Set headerRow = wsSource.Rows(1)
    
    On Error Resume Next
    For Each cell In wsSource.Range("B2:B" & lastRow)
        uniqueTerms.Add cell.Value, CStr(cell.Value)
    Next cell
    On Error GoTo 0
    
    For Each term In uniqueTerms
        Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsNew.Name = term
        headerRow.Copy wsNew.Rows(1)
        
        newRow = 2
        For Each cell In wsSource.Range("B2:B" & lastRow)
            If cell.Value = term Then
                wsSource.Rows(cell.Row).Copy wsNew.Rows(newRow)
                newRow = newRow + 1
            End If
        Next cell
    Next term
End Sub

Did you try the formula suggested by Cubist in post #17 ?