Challlenging COUNT IFS?
in [Programming]
|
Prev: Amnesty Bill
Next: List details in another sheet
From: MikeF on 3 May 2010 17:56 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
From: joel on 3 May 2010 18:33 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/showthread.php?t=200230 http://www.thecodecage.com/forumz
From: OssieMac on 3 May 2010 18:37 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 > >
From: p45cal on 3 May 2010 18:47 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/showthread.php?t=200230 http://www.thecodecage.com/forumz
From: MikeF on 3 May 2010 19:09
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 > > > > |