| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting color filled cells? For an Idiot. | Excel Discussion (Misc queries) | |||
| How do I avoid fields being automatically filled in by default? | New Users to Excel | |||
| Counting filled cells in excel | Excel Worksheet Functions | |||
| Counting blank and filled cells within a range. | Excel Discussion (Misc queries) | |||
| How to link cells and keep number format altogether | Excel Discussion (Misc queries) | |||
Linear Mode
