IF in sumproduct

  • Thread starter Thread starter Soe
  • Start date Start date
S

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?
 
Hi
try the following for example to count all values in a range which are
noit #NA
=SUMPRODUCT(--NOT(ISNA(A1:A100)))
 
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)
 
Back
Top