# How to refer to current column in a formula?

1. ## 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. ## Re: How to refer to current column in a formula?

=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" <jmg092548.1tj8ih_1123650313.5692@excelforum-nospam.com> wrote
in message news:jmg092548.1tj8ih_1123650313.5692@excelforum-nospam.com...
>
> 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
>

3. ## 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" <jmg092548.1tj8ih_1123650313.5692@excelforum-nospam.com> wrote
in message news:jmg092548.1tj8ih_1123650313.5692@excelforum-nospam.com...
>
> 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
>

4. ## 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

5. ## 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

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

Biff

"jmg092548" <jmg092548.1tjxiu_1123682729.5723@excelforum-nospam.com> wrote
in message news:jmg092548.1tjxiu_1123682729.5723@excelforum-nospam.com...
>
> 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
>
>> Thanks again! -- Jim

>
> Biff
>
>
> --
> jmg092548
>
>
> ------------------------------------------------------------------------
> jmg092548's Profile:
> http://www.excelforum.com/member.php...o&userid=26119
>

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

#### 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