Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default 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,
Attached Files
File Type: zip Copy of QA Tracker - Sample.zip (83.4 KB, 40 views)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMPRODUCT and INDIRECT JPDS Excel Worksheet Functions 7 January 16th 10 12:16 AM
SUMPRODUCT & INDIRECT? lou031205 Excel Worksheet Functions 4 November 4th 07 03:07 AM
INDIRECT COUNTIF's?? alymcmorland[_22_] Excel Programming 1 November 9th 05 12:51 PM
sumproduct indirect nowfal[_30_] Excel Programming 0 August 6th 05 08:45 PM
INDIRECT WITH COUNTIF Malcolm Austin Excel Worksheet Functions 2 November 26th 04 08:50 PM


All times are GMT +1. The time now is 01:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"