Enter the following.
A1=0, B1=0
A2=0, B2=0
A3=2
Then, enter this formula into A4.
=COUNT(IF(OFFSET(A1,0,0,A3,1)>0,IF(OFFSET(B1,0,0,A3,1)>0,OFFSET(A1,0,0,A3,1))))
As specified, the formula in A4 will correctly return the result, 0.
Now change the value in A3 to 1.
Now suddenly the formula result value in A4 is not correct, the result will be 1 (not the correct answer, 0)
In fact, when the range is 1 cell, you always get 1 as the COUNT answer, even though the logical evaluates to False.
With any value >1, the formula works correctly and will give the correct count of the cells that meet the logical criteria, whatever that criteria is.
Is there any way to make this formula work for the case that the range turns out to be of length 1, as well as for all other lengths?
In a complex spreadsheet in which the equivalent of the value in A3, above, might be 1 or might be a number >1, it does create a problem if the formula returns the wrong result in the single case where the value A3 is 1, but the correct result in all other cases.
Thanks!
A1=0, B1=0
A2=0, B2=0
A3=2
Then, enter this formula into A4.
=COUNT(IF(OFFSET(A1,0,0,A3,1)>0,IF(OFFSET(B1,0,0,A3,1)>0,OFFSET(A1,0,0,A3,1))))
As specified, the formula in A4 will correctly return the result, 0.
Now change the value in A3 to 1.
Now suddenly the formula result value in A4 is not correct, the result will be 1 (not the correct answer, 0)
In fact, when the range is 1 cell, you always get 1 as the COUNT answer, even though the logical evaluates to False.
With any value >1, the formula works correctly and will give the correct count of the cells that meet the logical criteria, whatever that criteria is.
Is there any way to make this formula work for the case that the range turns out to be of length 1, as well as for all other lengths?
In a complex spreadsheet in which the equivalent of the value in A3, above, might be 1 or might be a number >1, it does create a problem if the formula returns the wrong result in the single case where the value A3 is 1, but the correct result in all other cases.
Thanks!