auto fill question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marshall.w.garland@gmail.com - 05 Apr 2007 16:24 GMT

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?

Reply to this Message

whicks - 05 Apr 2007 16:35 GMT

On Apr 5, 10:24 am, marshall.w.garl...@gmail.com wrote:

> hello all-
>
[quoted text clipped - 18 lines]
>
> 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.

Reply to this Message

Peo Sjoblom - 05 Apr 2007 16:40 GMT

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

Signature

Regards,

Peo Sjoblom

> hello all-
>
[quoted text clipped - 18 lines]
>
> any ideas?

Reply to this Message

shanedevenshire@sbcglobal.net - 06 Apr 2007 17:46 GMT

On Apr 5, 8:24 am, marshall.w.garl...@gmail.com wrote:

> hello all-
>
[quoted text clipped - 18 lines]
>
> any ideas?

Hi,

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

Fill it down.

Cheers,
Shane Devenshire

Reply to this Message

Rate this thread: