Formula help - Average for 2 worksheets

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

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.

Can anyone help?

TIA
 
The best way is probably to create the different data on each sheet

=SUMIF(C2:C100,"mine",G2:G100)

and

=COUNTIF(C2:C100,"mine")

then use something like this on the third sheet

=SUM(Sheet1:Sheet2!O2)/SUM(Sheet1:Sheet2!O3)

where O2 and O3 would hold the formulas on each sheet

With one ugly formula

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2}&"'!C2:C100"),"mine",INDIRECT("'She
et"&{1,2}&"'!G2:G100")))/SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&{1,2}&"'!C2:C1
00"),"mine"))
 
Hi

Average is itself very simple:

Sum/count

You can probably do countif and sumif on both ranges to get this done with criteria.
 
Yes, I am aware that "Average" is itself very simple, but
my formula with multiple criteria on each sheet makes
writing the formula that works more complex.

So far I have used an array that sums from both worksheets
based on the criteria, but I am stuck on the "count"
portion for each sheet.

The count must also look for the criteria of the location
and count for value in cells g3:g1000 from both worksheets
that have a value (some cells may be null). Below is the
start of the array formula that sums.....can you help with
the count?

{=SUM(('DON survey'!C3:C1000="Bettendorf")*('DON survey'!
G3:G1000)+('Statnurse survey'!C3:C1000="Bettendorf")*
('Statnurse survey'!G3:G1000)}

TIA,
Jan
 
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"))
 
Minor problem with formula and unfortunately my knowledge of how to correct is limited so any further help you can provide would be greatly appreciated.

For the average.....I need the countif formula to only count when there is a value in G1:G1000.

Example: Each worksheet "Don survey" and "Statnurse survey" have 4 rows of data for Bettendorf, but not all cells in each worksheet column of g1:g1000 have a value. The values in the column, g1:g1000, may consist of: null, 1, 2, 3, 4, 5, 6, or 7. The total sum for bettendorf in column g1:g1000 of both worksheets is 40, but there are only 7 cells that have a value greater than null. Instead of dividing the sum by 7, it divides by 8 giving me the wrong average of 5 instead of 5.7.

Can you help?

TIA.
Jan
 
Back
Top