Challlenging COUNT IFS?
 

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.

Cant 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 theres 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

Challlenging COUNT IFS?
 

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

=if(countif(d23:o23,0)+countif(d23:o23,1)+countif( d23:o23,"")=12,1,0)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200230

http://www.thecodecage.com/forumz

Challlenging COUNT IFS?
 

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.

Cant 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 theres 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

Challlenging COUNT IFS?
 

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200230

http://www.thecodecage.com/forumz

Challlenging COUNT IFS?
 

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.

Cant 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 theres 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

Challlenging COUNT IFS?
 

On Mon, 3 May 2010 14:56:02 -0700, 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


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