Challlenging COUNT IFS?

[MikeF]

This has become quite challenging.

IF in range d23:023 there are only zeros, nothing, 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

 

[joel]

You are testing 12 columns. Yyou want to add up the countif test fo
0,1and nothing seperately. The results should be 12 if these are th
only results

=if(countif(d2323,0)+countif(d2323,1)+countif(d2323,"")=12,1,0)

 

[OssieMac]

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)

 

[p45cal]

or
=IF((COUNTIF(D23:O23,"")+COUNTIF(D23:O23,0))=12,0,1)
or
=IF((COUNTBLANK(D23:O23)+COUNTIF(D23:O23,0))=12,0,1)

 

[MikeF]

Ossie,

Thanx, that provided the correct result.
Much appreciated.

- Mik

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

This has become quite challenging.

IF in range d23:023 there are only zeros, nothing, 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

 

[Ron Rosenfeld]

This has become quite challenging.

IF in range d23:023 there are only zeros, nothing, 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