Problem with formula involving IF and OFFSET

[TomCon]

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!

 

[gaz_chops]

Is it because when a zero is entered the result is false, as you have no value for false then it will use false or 0, as 0 is a number it will count as 1.

Try this, by using "" for the false value it won't count as a number.
=COUNT(IF(OFFSET(A1,0,0,A3,1)>0,IF(OFFSET(B1,0,0,A3,1)>0,OFFSET(A1,0,0,A3,1)),""))

 

[jasonb75]

From the help file

• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• Arguments that are error values or text that cannot be translated into numbers are not counted.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.

Because you have left the FALSE arguments of your IF's empty, the resulting logical value is counted when it is a single cell, multiple cells come under array or reference.

To fix it you simply need to add something to the FALSE arguments that will not be counted, a nullstring for example

=COUNT(IF(OFFSET(A1,0,0,A3,1)>0,IF(OFFSET(B1,0,0,A3,1)>0,OFFSET(A1,0,0,A3,1),""),""))

 

[TomCon]

From the help file

• Logical values and text representations of numbers that you type directly into the list of arguments are counted.
• Arguments that are error values or text that cannot be translated into numbers are not counted.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.

Because you have left the FALSE arguments of your IF's empty, the resulting logical value is counted when it is a single cell, multiple cells come under array or reference.

To fix it you simply need to add something to the FALSE arguments that will not be counted, a nullstring for example

=COUNT(IF(OFFSET(A1,0,0,A3,1)>0,IF(OFFSET(B1,0,0,A3,1)>0,OFFSET(A1,0,0,A3,1),""),""))

Wow thanks, that solves the problem as does gaz_chops reply as well. Can always count on this board for great diagnosis! Thanks again. Did they do away with "this is the solution" button? I just "liked" both of the posts above!

 

[gaz_chops]

You're welcome, don't remember a "solution" button, but defo isn't one now.