Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Excel Count characters in a textbox to display character count? | Excel Programming | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |