sumif issue

You might be better doing this one by macro which searches all the tabs and compiles the list on the summary tab depending upon what it finds. I guess that you will need to be careful that any one technician is given more than one ID on the separate tabs - even a slight difference in a way that the name was written would be enough to produce confusing results.

You'll need something more that that you can record if you want to loop through all the sheets. If you want to find out more, have a look at the for...next loop in VBA help. (Alt F11 then help).

Are you able to attach a copy of the workbook so that we can see the detail of what you are trying to achieve? - it should be then possible to give more useful advice.

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