sumproduct with COUNTIF With An INDIRECT

[angie.chang]

I have multiple worksheets within the workbook. There are tab 1,2, 3,
etc. Each tab represent days of month(May).

I have a rep stats summary tab to sum up by rep. I would like to know if
there is a easier way to create this formula? A rep may be listed more
than one time in a day or per tab.


Attached is a copy of the excel. I am trying to see if there is a faster
to sum up all worksheets and count rep in the summary tab.

Thanks,


+-------------------------------------------------------------------+
|Filename: Copy of QA Tracker - Sample.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=874|
+-------------------------------------------------------------------+



--
angie.chang

[GS]

Try this UDF! It needs to be stored in a standard module to be used as
a worksheet formula. To do this...

keyboard Alt+F11 to open the VBE
select Insert>>Module on the menubar

..then paste the following into the empty window.

Option Explicit

Function Get_RepStats&(Rep As Range)
Application.Volatile
Dim wks
Const sRng$ = "$C$2:$C$61" '//edit to suit
For Each wks In ActiveWorkbook.Worksheets
If Not wks.Name = "Rep Stats" Then
Get_RepStats = Get_RepStats _
+ WorksheetFunction.CountIf(wks.Range(sRng), Rep)
End If
Next 'wks
End Function

To use it, select C3:C150 on "Rep Stats" and type the following
formula...

=get_repstats($B3)

..then hold down the 'Ctrl' key and press 'Enter'.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion