This has become quite challenging.

IF in range d23:023 there are only zeros, nothing[s], or a mix of both,
would like cell q23 to return zero, otherwise 1.

Can’t use a sum because sometimes there are offsetting numbers, ie +100 and
-100 in the same row that would return 0.
Some cells in the range are a formula [which is why there’s a zero], others
are just blank.

The end result is to import a large table into Access, using row q as import
criteria, so Access does not import any rows that are meaningless.
In other words, if *something* is in the row, put a 1 in column q, if there
is nothing meaningful in the row [zeros or nothings] put a zero in column q.

Have tried numerous COUNT IFS formulas to no avail.

Any assistance would be sincerely appreciated.

Thanx,
- Mike

Hi Mike,

Hope I have understood the question correctly.

Try the following formula. CountIf <> zero treats blanks as <> zero
therefore need to count the blanks separately and subtract them.

=IF(COUNTIF(D23:O23,"<>0")-COUNTBLANK(D23:O23)>0,1,0)


--
Regards,

OssieMac


"MikeF" wrote:

>
> This has become quite challenging.
>
> IF in range d23:023 there are only zeros, nothing[s], or a mix of both,
> would like cell q23 to return zero, otherwise 1.
>
> Can’t use a sum because sometimes there are offsetting numbers, ie +100 and
> -100 in the same row that would return 0.
> Some cells in the range are a formula [which is why there’s a zero], others
> are just blank.
>
> The end result is to import a large table into Access, using row q as import
> criteria, so Access does not import any rows that are meaningless.
> In other words, if *something* is in the row, put a 1 in column q, if there
> is nothing meaningful in the row [zeros or nothings] put a zero in column q.
>
> Have tried numerous COUNT IFS formulas to no avail.
>
> Any assistance would be sincerely appreciated.
>
> Thanx,
> - Mike
>
>

Ossie,

Thanx, that provided the correct result.
Much appreciated.

- Mik


"OssieMac" wrote:

> Hi Mike,
>
> Hope I have understood the question correctly.
>
> Try the following formula. CountIf <> zero treats blanks as <> zero
> therefore need to count the blanks separately and subtract them.
>
> =IF(COUNTIF(D23:O23,"<>0")-COUNTBLANK(D23:O23)>0,1,0)
>
>
> --
> Regards,
>
> OssieMac
>
>
> "MikeF" wrote:
>
> >
> > This has become quite challenging.
> >
> > IF in range d23:023 there are only zeros, nothing[s], or a mix of both,
> > would like cell q23 to return zero, otherwise 1.
> >
> > Can’t use a sum because sometimes there are offsetting numbers, ie +100 and
> > -100 in the same row that would return 0.
> > Some cells in the range are a formula [which is why there’s a zero], others
> > are just blank.
> >
> > The end result is to import a large table into Access, using row q as import
> > criteria, so Access does not import any rows that are meaningless.
> > In other words, if *something* is in the row, put a 1 in column q, if there
> > is nothing meaningful in the row [zeros or nothings] put a zero in column q.
> >
> > Have tried numerous COUNT IFS formulas to no avail.
> >
> > Any assistance would be sincerely appreciated.
> >
> > Thanx,
> > - Mike
> >
> >

On Mon, 3 May 2010 14:56:02 -0700, MikeF <(E-Mail Removed)>
wrote:

>
>This has become quite challenging.
>
>IF in range d23:023 there are only zeros, nothing[s], or a mix of both,
>would like cell q23 to return zero, otherwise 1.
>
>Can’t use a sum because sometimes there are offsetting numbers, ie +100 and
>-100 in the same row that would return 0.
>Some cells in the range are a formula [which is why there’s a zero], others
>are just blank.
>
>The end result is to import a large table into Access, using row q as import
>criteria, so Access does not import any rows that are meaningless.
>In other words, if *something* is in the row, put a 1 in column q, if there
>is nothing meaningful in the row [zeros or nothings] put a zero in column q.
>
>Have tried numerous COUNT IFS formulas to no avail.
>
>Any assistance would be sincerely appreciated.
>
>Thanx,
>- Mike
>

Try:

This formula must be **array-entered**:

=--OR(D23:O23<>0)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

The above methods require that blanks are truly empty cells, and do not contain
formulas that return a null string ("").

If some of the cells might contain a null string, then try:

**array-entered**

=--OR((D23:O23<>0)*(D23:O23<>""))

--ron