If none of your conditions are met your formula should return 0. When none
of the conditions are met are there any #N/A errors in *any* of the
referenced ranges?

--
Biff
Microsoft Excel MVP


"Phil" wrote in message
...
How to supress #N/A in an array formula.

Using Excel 2003.

The formula below calculates where a call has been replied to within
it's alllocated target time.

=SUM((('Sheet1'!$O$3:$O$6000=A3)*('Sheet'!$I$3:$I$ 6000<"")*('Sheet1'!
$N$3:$N$6000="NO")))
A B C

A. Cell A3 contains a label to a period of time, say a specific week,
lets say week 1.

B. Ensures thats the cell containing the completion indicator is not
blank, i.e. the action (reply) has been performed.

C. Determines if the target was failed.


So, my criteria are in the specified time period if the completion
indicator is not blank (this would contain a date that the action was
taken) and the action did not fail it's target (indicated by a "NO")
then it would be included in the total returned and so forth.

My problem here is that if during a given time period no calls were
booked, no data matches week 1 in the target sheet, then this formula
automatically returns #N/A, how do I prevent this.
If I were doing a simple LOOKUP I'd use
=IF(ISNA(VLOOKUP(..x..y..z..)),"NO CALLS",VLOOKUP(..x..y..z..))) etc

Any ideas anyone?

Many thanks, in anticipation.

Phil.