SumIf Across Sheets (2000)

Topic

New Reply

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= A2 ,IF(Jan!$B$1:$B$200= B2 ,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
( A2 = person’s last name B2 = 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.

Reply | Quote

Replies

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

Reply | Quote

(Edited by JohnBF on 08-Jul-03 13:49. Example attached.)

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 Rory 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.

Reply | Quote