Avoid counting #N/A filled cells, or avoiding #N/A altogether

  • Thread starter Thread starter fr8dog
  • Start date Start date
F

fr8dog

I posted another question not to long ago that I was resolved, thanks to who
replied, however, now I've run into another problem.

I am entering data on a day to day basis, so I am getting the #N/A in the
cells that have functions and no data to process those functions. I am
trying to keep a running count of certain info as I go, but the #n/a is
obviously screwing up the count in those columns, is there a way to avoid
counting the #n/a cells until they're filled in??
 
Appreciate the quick replies to both my posts, how would I go about putting
that into a SUMPRODUCT function?

I would like to use this function to count and avoid the #N/A cells:

=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(R6:R243<0))
 
I think this array-entered** formula does what you want...

=SUM(IF(ISNA(R6:R243),"",(MOD(ROW(R6:R243),3)=0)*(R6:R243=0)*(R6:R243<>"")))

** commit this formula with Ctrl+Shift+Enter, not just Enter by itself

Rick
 
you could try

=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(IF(ISNUMBER(R6:R243),R6:R243<0)))

but it has to be array entered w/Cntrl+Shift+Enter. alternatively, maybe
you could modify your formulas in column R to return a 0 instead of N/A.
 
Thanks Rick, got it working. Appreciate it.

Rick Rothstein (MVP - VB) said:
I think this array-entered** formula does what you want...

=SUM(IF(ISNA(R6:R243),"",(MOD(ROW(R6:R243),3)=0)*(R6:R243=0)*(R6:R243<>"")))

** commit this formula with Ctrl+Shift+Enter, not just Enter by itself

Rick
Click to expand...
 
=SUMPRODUCT(--(MOD(ROW(R6:R243),3)=0),--(R6:R243<0))
=SUM(IF(ISNA(R6:R243),"",(MOD(ROW(R6:R243),3)=0)*(R6:R243=0)*(R6:R243<>"")))
Click to expand...

Based on the OP's original formula it looks like they want to count numbers
<0 so you can eliminate that last array:

=SUM(IF(ISNA(R6:R243),"",(MOD(ROW(R6:R243),3)=0)*(R6:R243<0)))
 
Back
Top