+ Reply to Thread
Results 1 to 5 of 5

How to refer to current column in a formula?

  1. #1
    Registered User
    Join Date
    08-10-2005
    Posts
    7

    How to refer to current column in a formula?

    Say I've named the range of rows 10 through 50 as "Rowset."

    Then I want to put a formula in C8 that returns how many cells in C10:C50 are equal to "0".

    I can use COUNTIF(C:C Rowset,"0"), which works fine.

    But what if I also want to do the same thing in D8, E8, etc. Do I have to explicity say

    COUNTIF(D:D Rowset,"0")
    COUNTIF(E:E Rowset,"0")

    etc.? Or is there some way I can refer to the current column without naming it?

    I realize I can define the first formula as above and then copy and paste to get corresponding formulas in the other cooumns. I just wondered if there was one formula I could define so that it would work the same in any column.

    Thanks in advance for any help!

    Jim Guinness
    Eastern Massachusettts, USA

  2. #2
    Max
    Guest

    Re: How to refer to current column in a formula?

    Perhaps try instead in C8:
    =COUNTIF(OFFSET($A$10:$A$50,,COLUMNS($A$1:B1)),0)

    which returns the same as: = COUNTIF(C:C Rowset,0)

    but provides the flexibility to simply fill across from C8 to IV8
    to return the equivalent counts for cols D, E, F ... IV
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "jmg092548" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Say I've named the range of rows 10 through 50 as "Rowset."
    >
    > Then I want to put a formula in C8 that returns how many cells in
    > C10:C50 are equal to "0".
    >
    > I can use COUNTIF(C:C Rowset,"0"), which works fine.
    >
    > But what if I also want to do the same thing in D8, E8, etc. Do I have
    > to explicity say
    >
    > COUNTIF(D:D Rowset,"0")
    > COUNTIF(E:E Rowset,"0")
    >
    > etc.? Or is there some way I can refer to the current column without
    > naming it?
    >
    > I realize I can define the first formula as above and then copy and
    > paste to get corresponding formulas in the other cooumns. I just
    > wondered if there was one formula I could define so that it would work
    > the same in any column.
    >
    > Thanks in advance for any help!
    >
    > Jim Guinness
    > Eastern Massachusettts, USA
    >
    >
    > --
    > jmg092548
    > ------------------------------------------------------------------------
    > jmg092548's Profile:

    http://www.excelforum.com/member.php...o&userid=26119
    > View this thread: http://www.excelforum.com/showthread...hreadid=394467
    >




  3. #3
    Biff
    Guest

    Re: How to refer to current column in a formula?

    Hi!

    I'm assuming the Rowset range is A10:IV50

    =COUNTIF(INDEX(Rowset,,COLUMN()),"0")

    So, whatever column the formula is entered in will be the column range
    argument.

    Example: if the formula is entered in A8 then the Countif range will be
    A10:A50
    If the formula is entered in E8 then the Countif range will be E10:E50.

    Are you sure you want "0" ?

    Excel treats all quoted values as TEXT.

    Biff

    "jmg092548" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Say I've named the range of rows 10 through 50 as "Rowset."
    >
    > Then I want to put a formula in C8 that returns how many cells in
    > C10:C50 are equal to "0".
    >
    > I can use COUNTIF(C:C Rowset,"0"), which works fine.
    >
    > But what if I also want to do the same thing in D8, E8, etc. Do I have
    > to explicity say
    >
    > COUNTIF(D:D Rowset,"0")
    > COUNTIF(E:E Rowset,"0")
    >
    > etc.? Or is there some way I can refer to the current column without
    > naming it?
    >
    > I realize I can define the first formula as above and then copy and
    > paste to get corresponding formulas in the other cooumns. I just
    > wondered if there was one formula I could define so that it would work
    > the same in any column.
    >
    > Thanks in advance for any help!
    >
    > Jim Guinness
    > Eastern Massachusettts, USA
    >
    >
    > --
    > jmg092548
    > ------------------------------------------------------------------------
    > jmg092548's Profile:
    > http://www.excelforum.com/member.php...o&userid=26119
    > View this thread: http://www.excelforum.com/showthread...hreadid=394467
    >




  4. #4
    Registered User
    Join Date
    08-10-2005
    Posts
    7

    Re: How to refer to current column in a formula?

    Hi!

    I'm assuming the Rowset range is A10:IV50

    > correct

    =COUNTIF(INDEX(Rowset,,COLUMN()),"0")

    > good, thanks! two functions I'm not familiar with

    So, whatever column the formula is entered in will be the column range
    argument.

    Example: if the formula is entered in A8 then the Countif range will be
    A10:A50
    If the formula is entered in E8 then the Countif range will be E10:E50.

    Are you sure you want "0" ?

    Excel treats all quoted values as TEXT.

    > I'm not sure why I quoted it, I'd have thought it'd be treated as text also. It works that way (surprisingly), but also works without, so I'll not use them.

    > Did you post your reply from Excelforum? If so, is there an easy way to reply to a message as one does with most email programs -- i.e. with the Subject: included and the original message quoted? (I didn't see anything about this on the Excelforum site.)

    > Thanks again! -- Jim

    Biff
    Jim Guinness
    Eastern Massachusetts, USA

  5. #5
    Biff
    Guest

    Re: How to refer to current column in a formula?

    Hi!

    > Did you post your reply from Excelforum? If so, is there an easy way
    >to reply to a message as one does with most email programs -- i.e. with
    >the Subject: included and the original message quoted? (I didn't see
    >anything about this on the Excelforum site.)


    No, I access using Outlook Express. The Excelforum is just a "portal" that
    links to these newsgroups.

    Biff

    "jmg092548" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi!
    >
    > I'm assuming the Rowset range is A10:IV50
    >
    >> correct

    >
    > =COUNTIF(INDEX(Rowset,,COLUMN()),"0")
    >
    >> good, thanks! two functions I'm not familiar with

    >
    > So, whatever column the formula is entered in will be the column range
    > argument.
    >
    > Example: if the formula is entered in A8 then the Countif range will
    > be
    > A10:A50
    > If the formula is entered in E8 then the Countif range will be
    > E10:E50.
    >
    > Are you sure you want "0" ?
    >
    > Excel treats all quoted values as TEXT.
    >
    >> I'm not sure why I quoted it, I'd have thought it'd be treated as

    > text also. It works that way (surprisingly), but also works without, so
    > I'll not use them.
    >
    >> Did you post your reply from Excelforum? If so, is there an easy way

    > to reply to a message as one does with most email programs -- i.e. with
    > the Subject: included and the original message quoted? (I didn't see
    > anything about this on the Excelforum site.)
    >
    >> Thanks again! -- Jim

    >
    > Biff
    >
    >
    > --
    > jmg092548
    >
    >
    > ------------------------------------------------------------------------
    > jmg092548's Profile:
    > http://www.excelforum.com/member.php...o&userid=26119
    > View this thread: http://www.excelforum.com/showthread...hreadid=394467
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1