Sum/Count multipe worksheets with criteria for Average

[Jan]

Need help again. Please read my initial message below.
The reply I received does work, but unfortunately, I
failed to state that I wanted to count the cells that have
a value in column g3:g1000 if it met the first criteria of
location. The reply I received sums correctly, but counts
the specific location to use as the divisor. I need to
count "non empty cells" for the specific location. The
values that can be entered in a cell are 1 thru 7 or null
(there are no zeros).

My first message: <<Using Excel 2002. I have 2 worksheets
that contain lists;each showing sites and specific data. I
want to combine the data from each sheet when they each
match a location and average the data from a specific
column on each worksheet. Example: Sheet 1 - locations
entered in column C; data to average in column g. Sheet 2 -
locations entered in column C; data to average in column
g If location on sheet 1 & 2 = "mine", then average data
from column g on sheets 1 & 2. >>
Reply given:
It definitely helps if you would have posted the real
names of the sheets instead of using Sheet 1 and 2
Try this formula =SUMPRODUCT(SUMIF(INDIRECT("'"&{"DON
Survey";"Statnurse survey"}&"'!
C3:C1000"),"Bettendorf",INDIRECT("'"&{"DON
Survey";"Statnurse survey"}&"'!G3:G1000")))/SUMPRODUCT
(COUNTIF(INDIRECT("'"&{"DON Survey";"Statnurse survey"}&"'!
C3:C1000"),"Bettendorf"))

I have tried to revised the formula, but since my
knowledge of complex formulas is limited, I have been
unsuccessful. Can some one help?

TIA.
Jan

 

[Peo Sjoblom]

Try

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"DON Survey";"Statnurse
survey"}&"'!C3:C1000"),"Bettendorf",INDIRECT("'"&{"DON Survey";"Statnurse
survey"}&"'!G3:G1000")))/(SUMPRODUCT(--('DON
Survey'!C3:C1000="Bettendorf"),--('DON
Survey'!G3:G1000<>""))+SUMPRODUCT(--('Statnurse
survey'!C3:C1000="Bettendorf"),--('Statnurse survey'!G3:G1000<>"")))


If you plan to use more sheets you might want to download Laurent Longre's
Morefunc

http://longre.free.fr/


description

http://www.rhdatasolutions.com/morefunc/

 

[Jan]

Thank you so much. Sorry about not clearly stating what I
needed to do from the beginning.