Prev: Using english and non-English Excel commands simultaneously in a non-English installation
Next: Why does my text in a cell with wrap text and auto height get cut
From: fluffymitten on 11 Mar 2010 06:46
The following formula produces #N/A because some of the formulae in AB
produce #N/A. If I overwrite or remove the formulae producing the error, it
calculates correctly but I then lose information. How can I rewrite this to
essentially ignore errors - I would put in a NOT(ISERROR(blah blah)) but
can't see where to fit it in.

=SUMPRODUCT((A4:A278=1)*(AB4:AB278<TODAY()))

Many thanks for your help
Louise

-------------------------------------
fluffymitten<at>fluffymitten.com
From: Bob Phillips on 11 Mar 2010 06:54
Try this array formula

=SUM(IF(ISNUMBER(AB4:AB278),(A4:A278=1)*(AB4:AB278<TODAY())))

--

HTH

Bob

"fluffymitten" <fluffymitten(a)discussions.microsoft.com> wrote in message
news:09F51B9A-88CA-41CE-BC52-E4102A0046B6(a)microsoft.com...
> The following formula produces #N/A because some of the formulae in AB
> produce #N/A. If I overwrite or remove the formulae producing the error,
> it
> calculates correctly but I then lose information. How can I rewrite this
> to
> essentially ignore errors - I would put in a NOT(ISERROR(blah blah)) but
> can't see where to fit it in.
>
> =SUMPRODUCT((A4:A278=1)*(AB4:AB278<TODAY()))
>
> Many thanks for your help
> Louise
>
> -------------------------------------
> fluffymitten<at>fluffymitten.com


 | 
Pages: 1
Prev: Using english and non-English Excel commands simultaneously in a non-English installation
Next: Why does my text in a cell with wrap text and auto height get cut