Prev: Amnesty Bill
Next: List details in another sheet
From: Ron Rosenfeld on 3 May 2010 20:36
On Mon, 3 May 2010 14:56:02 -0700, MikeF <MikeF(a)discussions.microsoft.com>
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
First  |  Prev  | 
Pages: 1 2
Prev: Amnesty Bill
Next: List details in another sheet