sumif issue

You can try this macro.

Basically, it loops through all your sheets to gather data (excluding the one specified in the SUMMARY_NAME constant.

It then puts the summary information in the SUMMARY_NAME sheet.

It makes a couple of assumptions:

First, that your employee ids are all numeric and below 50,000. You can adjust this upward, but it won't work with non-numeric employee ids.

Second, that your description for being done is "complete".

Third, the sheet you want to skip is "summary".

Fourth, the columns listed in the example are the columns that you are actually using.

Plus, some other little things in comments.

Option Explicit

Const MAX_EMPLOYEE_ID = 50000
Const DESC_COMPLETE = "complete"
Const SUMMARY_NAME = "summary"

Public Sub Summarize()
  Dim WS As Worksheet
  Dim Start As Long
  Dim Finish As Long
  Dim Data(MAX_EMPLOYEE_ID, 1) As Long
  Dim i As Long
  Dim j As Long
  Dim current As Long

  'Gathering data... this goes through all sheets except the SUMMARY
  'sheet.
  For Each WS In Worksheets()
    If WS.Name <> SUMMARY_NAME Then
      Start = 2   'Assuming each sheet's data starts on Row 2
      Finish = WS.Range("A65536").End(xlUp).Row
          
      For i = Start To Finish
        With WS
          current = .Cells(i, 1)
          If (.Cells(i, 3) = DESC_COMPLETE) Then
            Data(current, 1) = Data(current, 1) + .Cells(i, 4)
          End If
          Data(current, 0) = Data(current, 0) + .Cells(i, 4)
        End With
      Next i
    End If
  Next

  'This puts the data in the SUMMARY sheet
  'For reference:
  '  i represents the row in our Data array.
  '  j represents the row in output, starting with row 2.
  j = 2
  For i = 0 To MAX_EMPLOYEE_ID
    If (Data(i, 0) <> 0 Or Data(i, 1) <> 0) Then
      With Worksheets(SUMMARY_NAME)
        .Cells(j, 1) = i
        .Cells(j, 2) = Data(i, 0)
        .Cells(j, 3) = Data(i, 1)
      End With
      j = j + 1
    End If
  Next i
End Sub

Scott

Ok, here you go.

Just to ramble on, to use the code above, you're probably better to just copy it into your spreadsheet with all your data as opposed to copying all the data, although that is just a personal opinion. (I would also make a backup, in case things get screwed up. Full of confidence, aren't I?)

All you have to do is go to VBA (Alt-F11), on the side, select the Workbook you want to put the macro in. Then choose Insert -> Module (Alt I - M), then paste the code in that module.

Then you can run it with Alt-F8 and choose the procedure from the list.

Scott

Playing with it is very understandable.

Here's one site, not much, but a little bit on what I was trying to show:

http://www.contextures.com/xlvba01.html

Here's another with a couple more screenshots. (Only the first bit applies)

http://www.fontstuff.com/vba/vbatut03.htm

I guess the key point is that when you hit Alt-F11, you have to select the name of the spreadsheet on the left side listing. (like they show in the screenshots on these sites)

(It's so much easier to show someone in person... and once you see it, it's fairly easy. The explanation in words is the hard part.)

With the copy I attached, you should be able to run it, provided your Macro Security is set to Medium. (Low is baddddd.... High won't allow you to run macros in general.) (Tools -> Options ... choose the Security tab, click the Macro Security button, change it to Medium.)

Scott