From: Jan Kronsell on 26 Feb 2010 03:42 I have this formula =SUM(IF(ISERROR(A1:A100),0,A1:A100)) entered as an array formula, to sum a column even if some of the cells contains error values. Now I wonder if it could be done using SUMPRODUCT or any other functions, to avoid the array formula? Jan From: Pete_UK on 26 Feb 2010 04:29 Try this (normally entered): =SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100) Hope this helps. Pete On Feb 26, 8:42 am, "Jan Kronsell" wrote:> I have this formula > >       =SUM(IF(ISERROR(A1:A100),0,A1:A100)) > > entered as an array formula, to sum a column even if some of the cells > contains error values. > > Now I wonder if it could be done using SUMPRODUCT or any other functions, to > avoid the array formula? > > Jan From: Mike H on 26 Feb 2010 06:00 Hi, Try this =SUMIF(A1:A100,"<1E100") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jan Kronsell" wrote: > I have this formula > > =SUM(IF(ISERROR(A1:A100),0,A1:A100)) > > > entered as an array formula, to sum a column even if some of the cells > contains error values. > > Now I wonder if it could be done using SUMPRODUCT or any other functions, to > avoid the array formula? > > Jan > > > . > From: Jan Kronsell on 26 Feb 2010 06:17 It did not. It returnes the error value it firtst meet in the column. Jan Pete_UK wrote:> Try this (normally entered): > > =SUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100) > > Hope this helps. > > Pete > > On Feb 26, 8:42 am, "Jan Kronsell" > wrote: >> I have this formula >> >> =SUM(IF(ISERROR(A1:A100),0,A1:A100)) >> >> entered as an array formula, to sum a column even if some of the >> cells contains error values. >> >> Now I wonder if it could be done using SUMPRODUCT or any other >> functions, to avoid the array formula? >> >> Jan From: Jan Kronsell on 26 Feb 2010 06:18 That worked, but can you tell me why? Jan Mike H wrote:> Hi, > > Try this > > =SUMIF(A1:A100,"<1E100") > >> I have this formula >> >> =SUM(IF(ISERROR(A1:A100),0,A1:A100)) >> >> >> entered as an array formula, to sum a column even if some of the >> cells contains error values. >> >> Now I wonder if it could be done using SUMPRODUCT or any other >> functions, to avoid the array formula? >> >> Jan >> >> >> .  |  Next  |  Last