Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Trenton, Ontario
    Thanked 0 Times in 0 Posts

    SumIf Across Sheets (2000)

    I apologize up front, I know this is a question that was asked awhile ago but I couldn't find it in the archives.

    I have a workbook that has 13 sheets (Jan, Feb...Dec & Total). Col A in each sheet has a last names and col B has first names, the rest of the cols have payroll data. The Total sheet has all of the employee names, the same headings across the top as the monthly sheets. To do what I want the formula would be something like

    {=SUM(IF(Jan!$A$1:$A$200= <font color=red>A2</font color=red> ,IF(Jan!$B$1:$B$200= <font color=red>B2</font color=red> ,Jan!$D$1:$D$200,0),0))+SUM(IF(Feb!$A$1:$A$200=A2, IF(Feb!$B$1:$B$200=B2,Feb!$D$1:$D$200,0),0))...... etc
    ( <font color=red>A2</font color=red> = person's last name <font color=red>B2</font color=red> = person's first name)

    I need a macro that will give me YTD totals for each employee for each col. (Col D = Time worked, E=Total Wage etc.)
    Thanks for any help you can offer.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: SumIf Across Sheets (2000)

    I have made this suggestion on other occasions:
    Instead of 13 sheets why not just 2
    Combine all the months together in one big list (perhaps adding a column for MONTH if needed)

    Then you can get a simple summary table using a pivot table, you can stats easily enough using datafilter combined with subtotals.

    It is much easier to work with 1 sheet rather than 12 for data tables.


  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 5 Times in 5 Posts

    Re: SumIf Across Sheets (2000)

    <P ID="edit" class=small>(Edited by JohnBF on 08-Jul-03 13:49. Example attached.)</P>SUMIF isn't 3D, and I agree with Steve that the best way to work with your data is to have it all in one worksheet. However, you could try this user-defined function if you wish:

    Function SumIfAcrossSheets(CriterionRange As Range, Criterion, SumRange As Range)
    ' by <!profile=Rory>Rory<!/profile> Archibald?
    Dim shtWS As Worksheet
    Dim strCrit As String, strValues As String, strSheet As String
    SumIfAcrossSheets = 0
    For Each shtWS In ActiveWorkbook.Worksheets
    strSheet = shtWS.Name
    strCrit = "'" & strSheet & "'!" & CriterionRange.Address(False, False, xlA1, , "A1")
    strValues = "'" & strSheet & "'!" & SumRange.Address(False, False, xlA1, , "A1")
    SumIfAcrossSheets = SumIfAcrossSheets + Application.WorksheetFunction.SumIf(Range(strCrit) , Criterion, Range(strValues))
    Next shtWS
    End Function

    One restriction is that it loops through all worksheets in the workbook, including the one it's used in, which will include your "Total" worksheet if you use it there, so if you want to excude data on the "Total" worksheet, the sumif'ed range for that worksheet must be blank.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts