SUMIF across multiple tabs based on "Name" criteria which is on differnt rows across multiple tabs

B

Bakul

New Member
Joined
Feb 1, 2009
Messages
3
Hello!

I have multiple worksheets with peoples names down Column A and the months (Jan - Dec) listed along row 2. Each worksheet represents a Project and lists all the resources (peoples names) along with the hours they will be working for each month of the year. A resource can be working on multiple projects, i.e. Bill Bob can be working on Project A, Project D and Project K. Not all resources are working on all projects.

Each project (worksheet tab) can have anything from 2 to 50 people working on the project (listed down Col A), so Project A may have only 5 people listed, and Project D, 35 people listed.

Each resource is listed in a different cell down Col A - i.e. Bill Bob is not listed in the same place down column A for each project. For example, for Project A his name could be in Cell A7, Project D in Cell A34 and Project K - cell A14.

I have a Master worksheet where I have listed all the resources from all the projects (tabs) - what I cannot do is make Excel look at all the tabs, find Bill Bob in Column A and then make it add all his monthly hours from Jan to Dec for all the projects that he is assigned to.

Example Project A on worksheet 2:

<TABLE style="WIDTH: 353pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=470 border=0 x:str><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=150 height=17></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Jan</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Feb</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Mar</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Apr</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>May</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill Bob</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>56</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Steven Rames</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>56</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>John King</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mary Mouse</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>56</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>67</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Keith Rock</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD></TR></TBODY></TABLE>

Example Project D on worksheet 3:
<TABLE style="WIDTH: 353pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=470 border=0 x:str><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 113pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=150 height=17></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Jan</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Feb</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Mar</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Apr</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>May</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Keith Rock</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>56</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Steven Rames</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>56</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mary Mouse</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>John King</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>56</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>67</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill Bob</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>12</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>23</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>34</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>45
</TD></TR></TBODY></TABLE>

Please can you help?

Thanks,

Bakul
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Well, it would make your life a lot easier if you calculated a total on each sheet individually, since this can be pulled by the sumif formula.

If you put in column g a sum of each row, then you can just add a sumif for each spreadsheet.

I don't know how many sheets you are using and this may not be the best method if you have tons of sheets.

Why not a statement such as

=SUMIF(Sheet2!A1:G6,"=Bill Bob",Sheet2!G:G)+SUMIF(Sheet1!A1:G6,"=Bill Bob",Sheet1!G:G)

Where column G is the total for all hours worked that project. Once you lay out the data this way, you should be able to click and drag down what you need (obviously where I have "Bill Bob" you would have a cell reference for the person).

Not sure if this will help or not, but hopefully moves you in a better direction.
 
Upvote 0
There are some 3-Dimensional array functions on the internet that should solve your situation. The only thing is they're unusual to implement.
http://www.dailydoseofexcel.com/archives/2006/02/

Here's the code to add the SUMIF3D2 function to your project:
Code: 
Function SumIf3D2(Range3D As String, Criteria As String, _
    Optional Sum_Range As String) As Variant
 
    Dim Sum As Double
    Dim vaRng1 As Variant, vaRng2 As Variant
    Dim i As Long
   
    Application.Volatile
   
    If Len(Sum_Range) = 0 Then
      Sum_Range = Range3D
    End If
 
    vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D)
    vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, Sum_Range)
   
    Sum = 0
    For i = LBound(vaRng1) To UBound(vaRng1)
        Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria, vaRng2(i))
    Next i
   
    SumIf3D2 = Sum
   
End Function
Function Parse3DRange2(wb As Workbook, _
                        SheetsAndRange As String) As Variant
 
    Dim sTemp As String
    Dim i As Long, j As Long
    Dim Sheet1 As String, Sheet2 As String
    Dim aRange() As Range
    Dim sRange As String
    Dim lFirstSht As Long, lLastSht As Long
    Dim rCell As Range
    Dim rTemp As Range
   
    On Error GoTo Parse3DRangeError
 
    sTemp = SheetsAndRange
       
    'if it's 3d, rtemp will be nothing
    On Error Resume Next
        Set rTemp = Range(sTemp)
    On Error GoTo Parse3DRangeError
   
    'if it's 3d, parse it
    If rTemp Is Nothing Then
       i = InStr(sTemp, "!")
       If i = 0 Then Err.Raise 9999
   
       'next line will generate an error if range is invalid
       'if it's OK, it will be converted to absolute form
       sRange = Range(Mid$(sTemp, i + 1)).Address
   
       sTemp = Left$(sTemp, i - 1)
       i = InStr(sTemp, ":")
       Sheet2 = Trim(Mid$(sTemp, i + 1))
       If i > 0 Then
           Sheet1 = Trim(Left$(sTemp, i - 1))
       Else
           Sheet1 = Sheet2
       End If
   
       'next lines will generate errors if sheet names are invalid
       With wb
           lFirstSht = .Worksheets(Sheet1).Index
           lLastSht = .Worksheets(Sheet2).Index
       
           'swap if out of order
           If lFirstSht > lLastSht Then
               i = lFirstSht
               lFirstSht = lLastSht
               lLastSht = i
           End If
             
           'load each cell into an array
           j = 0
           For i = lFirstSht To lLastSht
               For Each rCell In .Sheets(i).Range(sRange)
                   ReDim Preserve aRange(0 To j)
                   Set aRange(j) = rCell
                   j = j + 1
               Next rCell
           Next i
       End With
       
       Parse3DRange2 = aRange
    Else
        'range isn't 3d, so just load each cell into array
        For Each rCell In rTemp.Cells
            ReDim Preserve aRange(0 To j)
            Set aRange(j) = rCell
            j = j + 1
        Next rCell
       
        Parse3DRange2 = aRange
    End If
Parse3DRangeError:
    On Error GoTo 0
    Exit Function
End Function  'Parse3DRange
Open your workbook
Press Alt-F11 to open the VBEditor
Insert > Module
Paste in all the code above into the Module window that opens
Alt-F11 to close the VBEditor
Save your sheet.​

Now, a normal SUMIF just requires you enter ranges like normal cell references, but this SUMIF3D2 requires that everything except the criteria be entered as a string...which is unusual.

I set up 3 sheets like this:

Excel Workbook
ABCDEFGHIJKLMN
1NAMEJanFebMarAprMayJunJulAugSepOctNovDecTotal
2Bill Bob23343445566269778592100108784.2
3Steven Rames233445562343454749525456526.8
4John King14573431384552596572412.2
5Maye Mouse123445566782961091221351481621068
6Keith Rock122334344553606876849199678.6
Sheet3


Then, I used the SUMIF3D2 as follows:

=Sumif3D2("Sheet1:Sheet3!A2:A20",A2,"Sheet1:Sheet3!N2:N20")

...where A2 has the name in it I am searching for, and the N column on all three sheets has the "totals" for that sheet for each row.

The names can be in any position in Column A, it will find them.

Notice the underlined portions above are entered inside of quotes like a string, that is required for the searched range and the summed range. Don't forget that.
 
Upvote 0
Dear JBeaucaire,

Thanks for that - most apprecited !!! I like how this works.

However, what if I don't have a Total column? What if I want my Master (the one that sums up all the other worksheets) for each particular month?

Your solution works great with that Totals column, but I need to see the totals for each month too, i.e. the total hours Keith Rock worked for Jan, Feb, Mar, Apr etc from wherever his name appears on all the other worksheets.

Is this possible?

Again, I thank you for your response - it has definitely got me in the right direction...

Cheers,

Bakul
 
Upvote 0
The solution is the same for your new request. Just change the SUM_RANGE at the end of the formula. January is in the B column, so it changes to:

=Sumif3D2("Sheet1:Sheet3!A2:A20",A2,"Sheet1:Sheet3!B2:B20")

And if you do this for your summary page, you DON'T need to add the Totals column I used. You can just sum the total on your Summary Sheet.
 
Upvote 0
Dear JBeaucaire,

Okay, to add up Jan I just swapped out the N column for the Jan column name and it seems to work fine! I am not using a Total column.

However, when I imported the VB code into my real spreadsheet, and used my own Worksheet names (instead of Sheet1:Sheet3), I keep getting the #NAME? error.

Is there something I need to change in the VB code?

Here is the formula I am using in my Master worksheet (same as yours but with my worksheet names).

=Sumif3D2("Fixed Assets:Straight Line Rent!B10:B69",B10,"Fixed Assets:Straight Line Rent!G10:G69")

Any advice would be greatly appreciated!

Kind regards,

Bakul
 
Upvote 0
No, I played around with the varying name ranges, including names with spaces and non-sequential naming, everything I tried. As long as the first sheetname and the second sheet name represent the beginning and ending names of consecutively arranged sheets, it works. There must be something about your sheet we haven't discovered yet.
 
Upvote 0
jbeaucaire said:
There are some 3-Dimensional array functions on the internet that should solve your situation. The only thing is they're unusual to implement.
Daily Dose of Excel » 2006 » February

Here's the code to add the SUMIF3D2 function to your project:
Code: 
Function SumIf3D2(Range3D As String, Criteria As String, _
    Optional Sum_Range As String) As Variant
 
    Dim Sum As Double
    Dim vaRng1 As Variant, vaRng2 As Variant
    Dim i As Long
   
    Application.Volatile
   
    If Len(Sum_Range) = 0 Then
      Sum_Range = Range3D
    End If
 
    vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D)
    vaRng2 = Parse3DRange2(Application.Caller.Parent.Parent, Sum_Range)
   
    Sum = 0
    For i = LBound(vaRng1) To UBound(vaRng1)
        Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria, vaRng2(i))
    Next i
   
    SumIf3D2 = Sum
   
End Function
Function Parse3DRange2(wb As Workbook, _
                        SheetsAndRange As String) As Variant
 
    Dim sTemp As String
    Dim i As Long, j As Long
    Dim Sheet1 As String, Sheet2 As String
    Dim aRange() As Range
    Dim sRange As String
    Dim lFirstSht As Long, lLastSht As Long
    Dim rCell As Range
    Dim rTemp As Range
   
    On Error GoTo Parse3DRangeError
 
    sTemp = SheetsAndRange
       
    'if it's 3d, rtemp will be nothing
    On Error Resume Next
        Set rTemp = Range(sTemp)
    On Error GoTo Parse3DRangeError
   
    'if it's 3d, parse it
    If rTemp Is Nothing Then
       i = InStr(sTemp, "!")
       If i = 0 Then Err.Raise 9999
   
       'next line will generate an error if range is invalid
       'if it's OK, it will be converted to absolute form
       sRange = Range(Mid$(sTemp, i + 1)).Address
   
       sTemp = Left$(sTemp, i - 1)
       i = InStr(sTemp, ":")
       Sheet2 = Trim(Mid$(sTemp, i + 1))
       If i > 0 Then
           Sheet1 = Trim(Left$(sTemp, i - 1))
       Else
           Sheet1 = Sheet2
       End If
   
       'next lines will generate errors if sheet names are invalid
       With wb
           lFirstSht = .Worksheets(Sheet1).Index
           lLastSht = .Worksheets(Sheet2).Index
       
           'swap if out of order
           If lFirstSht > lLastSht Then
               i = lFirstSht
               lFirstSht = lLastSht
               lLastSht = i
           End If
             
           'load each cell into an array
           j = 0
           For i = lFirstSht To lLastSht
               For Each rCell In .Sheets(i).Range(sRange)
                   ReDim Preserve aRange(0 To j)
                   Set aRange(j) = rCell
                   j = j + 1
               Next rCell
           Next i
       End With
       
       Parse3DRange2 = aRange
    Else
        'range isn't 3d, so just load each cell into array
        For Each rCell In rTemp.Cells
            ReDim Preserve aRange(0 To j)
            Set aRange(j) = rCell
            j = j + 1
        Next rCell
       
        Parse3DRange2 = aRange
    End If
Parse3DRangeError:
    On Error GoTo 0
    Exit Function
End Function  'Parse3DRange
Open your workbook
Press Alt-F11 to open the VBEditor
Insert > Module
Paste in all the code above into the Module window that opens
Alt-F11 to close the VBEditor
Save your sheet.​

Now, a normal SUMIF just requires you enter ranges like normal cell references, but this SUMIF3D2 requires that everything except the criteria be entered as a string...which is unusual.

I set up 3 sheets like this:

Sheet3

*
A
B
C
D
E
F
G
H
I
J
K
L
M
N

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]
[TD="bgcolor: #CCFFFF"]NAME
[/TD]
[TD="bgcolor: #FFCC99"]Jan
[/TD]
[TD="bgcolor: #FFCC99"]Feb
[/TD]
[TD="bgcolor: #FFCC99"]Mar
[/TD]
[TD="bgcolor: #FFCC99"]Apr
[/TD]
[TD="bgcolor: #FFCC99"]May
[/TD]
[TD="bgcolor: #FFCC99"]Jun
[/TD]
[TD="bgcolor: #FFCC99"]Jul
[/TD]
[TD="bgcolor: #FFCC99"]Aug
[/TD]
[TD="bgcolor: #FFCC99"]Sep
[/TD]
[TD="bgcolor: #FFCC99"]Oct
[/TD]
[TD="bgcolor: #FFCC99"]Nov
[/TD]
[TD="bgcolor: #FFCC99"]Dec
[/TD]
[TD="bgcolor: #CCFFCC"]Total
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="bgcolor: #CCFFFF"]Bill Bob
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]62
[/TD]
[TD="align: right"]69
[/TD]
[TD="align: right"]77
[/TD]
[TD="align: right"]85
[/TD]
[TD="align: right"]92
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]108
[/TD]
[TD="bgcolor: #CCFFCC, align: right"]784.2
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="bgcolor: #CCFFFF"]Steven Rames
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"]49
[/TD]
[TD="align: right"]52
[/TD]
[TD="align: right"]54
[/TD]
[TD="align: right"]56
[/TD]
[TD="bgcolor: #CCFFCC, align: right"]526.8
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="bgcolor: #CCFFFF"]John King
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]52
[/TD]
[TD="align: right"]59
[/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"]72
[/TD]
[TD="bgcolor: #CCFFCC, align: right"]412.2
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="bgcolor: #CCFFFF"]Maye Mouse
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]67
[/TD]
[TD="align: right"]82
[/TD]
[TD="align: right"]96
[/TD]
[TD="align: right"]109
[/TD]
[TD="align: right"]122
[/TD]
[TD="align: right"]135
[/TD]
[TD="align: right"]148
[/TD]
[TD="align: right"]162
[/TD]
[TD="bgcolor: #CCFFCC, align: right"]1068
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="bgcolor: #CCFFFF"]Keith Rock
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]53
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]68
[/TD]
[TD="align: right"]76
[/TD]
[TD="align: right"]84
[/TD]
[TD="align: right"]91
[/TD]
[TD="align: right"]99
[/TD]
[TD="bgcolor: #CCFFCC, align: right"]678.6
[/TD]

</tbody>

Spreadsheet Formulas

Cell
Formula
N2
=SUM(B2:M2)
N3
=SUM(B3:M3)
N4
=SUM(B4:M4)
N5
=SUM(B5:M5)
N6
=SUM(B6:M6)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Then, I used the SUMIF3D2 as follows:

=Sumif3D2("Sheet1:Sheet3!A2:A20",A2,"Sheet1:Sheet3!N2:N20")

...where A2 has the name in it I am searching for, and the N column on all three sheets has the "totals" for that sheet for each row.

The names can be in any position in Column A, it will find them.

Notice the underlined portions above are entered inside of quotes like a string, that is required for the searched range and the summed range. Don't forget that.
Click to expand...


I have tried this and it worked wonderfully inside the workbook, but if I want to pull data from another workbook it fails. Is there away to work around this? The formula I am using in the workbook is:
Code: 
=Sumif3D2("END:START!C8",C8,"END:START!C10")

but need to pull data from another work book also such as
Code: 
=Sumif3D2("[JAN.XLSM]END:START!C8",C8,"[JAN.XLSM]END:START!C10")
 
Last edited:
Upvote 0
You may be in for a bit of a struggle. Dates are inordinately tempermental in Excel when it comes to "matching" stuff. Your A2 value needs to be in the EXACT same display format as the match range dates, only hope that it will work right all the time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
S
  • Solved
XLOOKUP with multiple criteria across multiple columns and rows
Replies
6
Views
918
soriaul
S
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top