Suppress the #N/A in the formula that returns it. Meaning the sum formula
does not returns
#N/A by itself, it must be in one or more of the ranges you are using in the
sum formula


--


Regards,


Peo Sjoblom


"Phil" wrote in message
...
On 6 May, 16:57, "T. Valko" wrote:
If none of your conditions are met yourformulashould 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 anarrayformula.

Using Excel 2003.

Theformulabelow 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 thisformula
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.- Hide quoted text -

- Show quoted text -

Okay, I guess that's correct, when the first criteria isn't met then
none of them will be, therefore I am really asking how to supress #N/A
when no criteria are met