sumproduct with COUNTIF With An INDIRECT
 

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,

sumproduct with COUNTIF With An INDIRECT
 

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 InsertModule 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