=SUMIF($A$2:$A$8, "Location",
Sheet2!$B$2:$B$8)/SUMPRODUCT(--($A$2:$A$8="Location"),
--(Sheet2!$B$2:$B$8<0))

"flumpuk" wrote:

Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?