Auto Extended Formula not working

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="","",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="","",$S185-$T185)

Let me know if this works for you

~Rob Walton


"Connie Martin" <[email protected]> wrote in message
news:[email protected]...
> 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="","",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

My 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:
http://support.microsoft.com/?id=231...22120121120120 and
scroll down to the part "When Auto Extended Formulas May Not Work". I found
this later and wonder if that's not the problem. It simply doesn't work for
every type of formula.

"Rob Walton" wrote:

> 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="","",$S185-$T185)
>
> Let me know if this works for you
>
> ~Rob Walton
>
>
> "Connie Martin" <[email protected]> wrote in message
> news:[email protected]...
> > 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="","",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
>
>