IF in sumproduct

[Soe]

a column hv dates, may also have #N/A values, when this column is used in
sumproduct function, the result can be #N/A.
i tried to use if statement to check if this is number first, however, it
doesn't work.
anybody wants to share what you have done successfully?

 

[Frank Kabel]

Hi
try the following for example to count all values in a range which are
noit #NA
=SUMPRODUCT(--NOT(ISNA(A1:A100)))

 

[Bob Phillips]

Hi Soe,

Frank's answer counts the cells that are not #N/A, I am guessing this is not
what you want.

If you want to sum a column ignoring #N/A try

=SUM(IF(NOT(ISNA(A1:A20)),A1:A20,0))

which is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

Or the faster (and shorter)

=SUMIF(A1:A20,"<>#N/A")

albeit not as flexible