Ok. I dont know if I am not just getting it or what. When I enter the formula
i get an error "#REF" I am generally pretty good a creating formulas in
excell but this one has be stumped. Would it be easier for me to send you the
file to look at because maybe I am not discribing it correct

"Peo Sjoblom" wrote:

You don't need to use a defined name if you don't know how to define it, you
can hard code the range instead

=SUMPRODUCT(SUMIF(INDIRECT("'"&Summary!$H$1:$H$31& "'!$A:$A"),
$A1,INDIRECT("'"&Summary!$H$1:$H$31&"'!$A:$A")))/$A1

However it is easier to define a name if you just select (highlight) the
range

Summary!$H$1:$H$31

then simply type the name you want in the namebox above column A and press
enter

(if you select a cell like A2 the namebox is the box that will tell you the
cell reference of the cell you selected, it's to the left above the header
of column A)




--


Regards,


Peo Sjoblom



"Florida Richard" wrote in
message ...
I couldnt get it to work. I found the name list but whatever i enter tells
me
it is not valid and do i enter Summary!$H$1:$H$31 into the "refers to "
cell.
Actually on my sheet its called "tracking" also i get and error of #NAME?
in
the cell i put the formula into

"Roger Govier" wrote:

Hi Richard

One way
Assuming your sheets are named 1,2,3 etc., create a list somewhere on
your
Summary sheet and give it a Name e.g. List
InsertNameDefineName List Refers to Summary!$H$1:$H$31

On your Summary sheet, enter the numbers 1 through 9 in cells A1:A9
In B1 enter
=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A:$A"),
$A1,INDIRECT("'"&list&"'!$A:$A")))/$A1

Copy down through B2:B9
--
Regards
Roger Govier



"Florida Richard" wrote in
message ...
I need assistance with a formula...

I have a workbook that tracks work performed for each day of the month.
there are 32 sheets 31 for each day of the month and 1 for productivity
report which take the totals for each day and puts it on to one page.
Column
A is the permit type which is type 1 thru 9. What I want to do now is
on a
new work sheet in the same workbook I would like to, for each day in
the
month, populate that page so it will tell me how many of each type of
permit
were done on a specific day. For example on day 1 the person processed
4
type
1's, 6 type 2's and so on and so on.

I would like the worksheet to auto populate based on the information
provided on the other 31 worksheets

Thank you for your help.