Tip:

=SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3))

Whenever you quote a range inside INDIRECT there's no need to include the $
signs. Since the range is evaluated as a text string it will *never* change
if the formula is copied. Makes it a bit easier to read and saves a couple
of keystrokes.

=SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!A2:A2000"),A3))

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
With your sheetnames in range G1:G17 try the below formula

=SUMPRODUCT(COUNTIF(INDIRECT("'" & G1:G17 & "'!$A$2:$A$2000"),A3))

If this post helps click Yes
---------------
Jacob Skaria


"AussieBec" wrote:

=SUMPRODUCT(--(A3=ALB!$A$2:$A$2000))

I have the above formula over 1000 lines which I need to sort the result
ascending/descending of those 1000 lines some point to different
worksheets
so when I sort they no longer point to the correct worksheet. How do I
use a
sumproduct to reference all worksheets (up to 17 worksheets) incidently
all
will have the same range A2:A2000 just different worksheet name.

ALB.UNKN (worksheet alb)
BAT (worksheet bat)
BAT.001 (worksheet bat)
BLA (worksheet bla)
BLA.001 (worksheet bla)
CRE (worksheet cre)
CRE.001 (worksheet cre)

I'm sure they will be a solution quicker than I typed this request.
Thanks
Bec