# Thread: SumIf Across Sheets (2000)

1. ## 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.)

2. ## 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.

Steve

3. ## 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
Application.Volatile
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.

#### Posting Permissions

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