auto fill question

[marshall.w.garland]

hello all-

i'm trying to use the auto-fill function to populate a column with a
'countif' function. although the formula is counting the number of
occurrences of 'x' in a given column, i want to autofill vertically
while the reference columns run horizontally. must the autofill
function operate in the same direction as the reference data? is there
a way around this?

by way of a generic example:

=COUNTIF('widgets'!L:L,"x")

so, when i pull down the autofill handle, the formula should change
incrementally to:

=COUNTIF('widgets'!M:M,"x")
=COUNTIF('widgets'!N:N,"x")

instead, it it's just duplicating the previous formula.

any ideas?

 

[whicks]

hello all-

i'm trying to use the auto-fill function to populate a column with a
'countif' function. although the formula is counting the number of
occurrences of 'x' in a given column, i want to autofill vertically
while the reference columns run horizontally. must the autofill
function operate in the same direction as the reference data? is there
a way around this?

by way of a generic example:

=COUNTIF('widgets'!L:L,"x")

so, when i pull down the autofill handle, the formula should change
incrementally to:

=COUNTIF('widgets'!M:M,"x")
=COUNTIF('widgets'!N:N,"x")

instead, it it's just duplicating the previous formula.

any ideas?

when copying or autofilling a formula 'down a column' excel only
changes row references. I suggest creating a totals row at the bottom
or top of your spreadsheet and that should give you the result you
want. Otherwise, you will have to manually write each formula.

 

[Peo Sjoblom]

Autofill won't work but you can use a formula inside the COUNTIF

=COUNTIF(INDEX('widgets'!L:IV,0,ROWS($A$1:A1)),"x")


note that you have to reference as much of the area that you plan to use
(L:IV)
will cover what 97-2003 versions can cope with

 

[shanedevenshire]

hello all-

i'm trying to use the auto-fill function to populate a column with a
'countif' function. although the formula is counting the number of
occurrences of 'x' in a given column, i want to autofill vertically
while the reference columns run horizontally. must the autofill
function operate in the same direction as the reference data? is there
a way around this?

by way of a generic example:

=COUNTIF('widgets'!L:L,"x")

so, when i pull down the autofill handle, the formula should change
incrementally to:

=COUNTIF('widgets'!M:M,"x")
=COUNTIF('widgets'!N:N,"x")

instead, it it's just duplicating the previous formula.

any ideas?

Hi,

=COUNTIF(OFFSET(M$1,0,ROW(A1)-1,65536),"x")

Fill it down.

Cheers,
Shane Devenshire