2000 Counting Same Range Across Multiple Sheets

I have been searching the web and wracking my brain trying to do this and now I am here (for one place to start) for help. Especially since I got the formula below from this forum.

1. I have a worksheet with multiple sheets. All the sheets except the last are labeled by department. The last sheet is labeled Totals. All the department sheets follow a template that I don't want to change so any totals or formulas I want to add, I want to do off of the Totals sheet.

2. For an example, I want to total all PCs with the model "Compaq 1.8" from all the sheets onto the Totals sheet. It doesn't seem like Countif can do multiple (or 3d) sheets. It can only do one sheet.

It looks like the only way to do this is to use a UDF (User Defined Function) or some other Function to do this.

I found a UDF that looks like it should work (see below). However, I have tried this function but all I get is the #NAME? error/message. I have tried multiple ways to simplify this function just to test that I can get any data from it but keep coming up with the #NAME? error.



Public Function CntIf3D(rng As Range, V As Variant, ParamArray arglist() As Variant)
Application.Volatile
CntIf3D = 0
For Each arg In arglist
CntIf3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + CntIf3D
Rem Next
End Function
If I could get the above code to work, It would be nice but I would have to add another department into every total for each new piece of equipment. Some code that would just use the sheet indexes would be nicer, that way any new department inserted before the TOTALS sheet would be automatically added in. Since I can't even get the above code to work, however, I don't want to waste my time trying to test code that would use the indexes.