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

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??

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

COUNT() does not count cells which contain #N/A
--
David Biddulph

"fr8dog" wrote in message
...

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??

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

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))



"David Biddulph" wrote:

COUNT() does not count cells which contain #N/A
--
David Biddulph

"fr8dog" wrote in message
...

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??

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

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


"fr8dog" wrote in message
...
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))



"David Biddulph" wrote:

COUNT() does not count cells which contain #N/A
--
David Biddulph

"fr8dog" wrote in message
...

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??

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

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.

"fr8dog" wrote:

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))



"David Biddulph" wrote:

COUNT() does not count cells which contain #N/A
--
David Biddulph

"fr8dog" wrote in message
...

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??

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

Thanks Rick, got it working. Appreciate it.

"Rick Rothstein (MVP - VB)" wrote:

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


"fr8dog" wrote in message
...
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))



"David Biddulph" wrote:

COUNT() does not count cells which contain #N/A
--
David Biddulph

"fr8dog" wrote in message
...

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??

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

=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<"")))

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)))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"fr8dog" wrote in message
...
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))



"David Biddulph" wrote:

COUNT() does not count cells which contain #N/A
--
David Biddulph

"fr8dog" wrote in message
...

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??