software

Does the auto extend list for formulas not work with this type of formula:
=COUNTIF($L$3:$L185,$L185) ?? I have another formula in the same
worksheet, which is: =IF(Q185=quot;quot;,quot;quot;,S185-T185) and the auto extended formula
function works with it when new data in typed in the next empty row, but
won't for the other formula. Any answers? Connie

Hi Connie,

It looks like you have a small error in your function. In the first
argument of the COUNTIF function, you have the beginning of the range
containing a static column AND row and the end of the range has a static
column but a dynamic row, the $ before either the column or row means that
it will not change as you auto-extend, it remains static. Try this:

=COUNTIF($L3:$L185,$L185)

For the IF function, it looks like you may NEED static columns, so try this:

=IF($Q185=quot;quot;,quot;quot;,$S185-$T185)

Let me know if this works for you

~Rob Waltonquot;Connie Martinquot; gt; wrote in message
...
gt; Does the auto extend list for formulas not work with this type of formula:
gt; =COUNTIF($L$3:$L185,$L185) ?? I have another formula in the same
gt; worksheet, which is: =IF(Q185=quot;quot;,quot;quot;,S185-T185) and the auto extended
gt; formula
gt; function works with it when new data in typed in the next empty row, but
gt; won't for the other formula. Any answers? ConnieMy formulas work fine. My first formula is counting how many times a number
repeats itself in the column. It starts at the top of the column with
=COUNTIF($L$3:$L3,$L3) and works on down the column picking out numbers that
repeat themselves and putting a number in as to how many times that number
has repeated itself in the column, or a simple 1 if it appears for the first
time. It must be as I have written it in order to do that. The second
formula works quite fine. The auto extend list function works with this
formula as it is. Your formula and mine give the same answer. Go to:
support.microsoft.com/?id=231...22120121120120 and
scroll down to the part quot;When Auto Extended Formulas May Not Workquot;. I found
this later and wonder if that's not the problem. It simply doesn't work for
every type of formula.

quot;Rob Waltonquot; wrote:

gt; Hi Connie,
gt;
gt; It looks like you have a small error in your function. In the first
gt; argument of the COUNTIF function, you have the beginning of the range
gt; containing a static column AND row and the end of the range has a static
gt; column but a dynamic row, the $ before either the column or row means that
gt; it will not change as you auto-extend, it remains static. Try this:
gt;
gt; =COUNTIF($L3:$L185,$L185)
gt;
gt; For the IF function, it looks like you may NEED static columns, so try this:
gt;
gt; =IF($Q185=quot;quot;,quot;quot;,$S185-$T185)
gt;
gt; Let me know if this works for you
gt;
gt; ~Rob Walton
gt;
gt;
gt; quot;Connie Martinquot; gt; wrote in message
gt; ...
gt; gt; Does the auto extend list for formulas not work with this type of formula:
gt; gt; =COUNTIF($L$3:$L185,$L185) ?? I have another formula in the same
gt; gt; worksheet, which is: =IF(Q185=quot;quot;,quot;quot;,S185-T185) and the auto extended
gt; gt; formula
gt; gt; function works with it when new data in typed in the next empty row, but
gt; gt; won't for the other formula. Any answers? Connie
gt;
gt;