Fysh wrote...
Ok Like I said before your solution worked. However, I have a
couple
questions. First how do you use this if the cell is null or has
N/A it, to
not include these when divding? Second how do you drag the
formula to
the other cells? For some reason I have to go in and edit each
cell on
the main worksheet.
...
"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("'"
&H1:H25&"'!D4"),"<0"))
...

Last question first. If you need to be able to fill the formula into
different cells, then you have to make the INDIRECT reference
relative. If you want to start off referencing the D4 cells in the
active cell's formula, use

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

As for your first question, what do you mean by a cell being 'null'?
There's a specific error value #NULL!, and using the term 'null' to
refer to cells evaluating to that error value is as likely as any
other meaning you may have had in mind. If you mean cells containing
nothing, no formula or constant numbers or text, then the proper
Excel term is 'blank', as in the ISBLANK formula.

If by 'null' you mean blank cells, you can't exclude them using
COUNTIF. Note that the AVERAGE function skips blank cells, so

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

would work if it were only blank cells (and cells containing text and
boolean values) you wanted to exclude from your averages.

As for cells evaluating to #N/A, they'd be included in SUM, so your
numerator would evaluate to #N/A, so the denominator would be
irrelevant - your 'average' would be #N/A. If you want to filter out
cells evaluating to #N/A from 3D references, you're going to have to
use the explicit approach. With a complete list of worksheet names
in, say, X1:X20 (change as needed), try the array formula

=AVERAGE(IF(ISNUMBER(1/N(INDIRECT("'"&$X$1:$X$20&"'!"
&CELL("Address",D4)))),N(INDIRECT("'"&$X$1:$X$20&" '!"
&CELL("Address",D4)))))

Note: the N() function calls are *MANDATORY*. INDIRECT passed an array
argument will return something that works like an array of range
references. Such arrays are undocumented in Excel, which is why I'll
only say they work like such rather than that they are such. Excel
won't accept them as arithmetic operands, but N() will convert them
to arrays of values, which Excel does accept. Lots of fun making
Excel function like a 3D spreadsheet.

Actually, the formula above will skip cells evaluating to any error
value. To restrict filtering just to those cells evaluating to #N/A
specifically, you'd need a longer formula involving the ERROR.TYPE
function.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!