Peo Sjoblom wrote...
You can't use countif over multiple sheets, there is a workaround
but it
requires some extra work

=SUM('PSNS:TRF KB'!D4)/
SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!D4"), "<0"))

where MySheets is a named range that holds a list of ALL you
sheet
names that you want to include, not only the first and last sheets
like in
the SUM formula, you need to put all the sheet names.
...

While this works, it may be better in the long run for the OP to
modify the cells that are currently evaluating to 0 so that they
evaluate to "" instead. Once that change were made, multiple
worksheet averages could be calculated with

=AVERAGE('PSNS:TRF KB'!D4)

But this rests on an assumption that the OP is using IF functions in
these cells that return strictly positive values when the condition
is satisfied and zeros otherwise. If this isn't the case, then the
OP's original formula, your formula and my formula and modifications
all miss the point - what do the zeros represent? If the values in
these cells could legitimately be nonpositive, then excluding zero
values will bias the averages, which is usually a very bad thing. If
the cells are actually blank, no need to exclude them.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!