Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT and INDIRECT | Excel Worksheet Functions | |||
SUMPRODUCT & INDIRECT? | Excel Worksheet Functions | |||
INDIRECT COUNTIF's?? | Excel Programming | |||
sumproduct indirect | Excel Programming | |||
INDIRECT WITH COUNTIF | Excel Worksheet Functions |