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


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

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" wrote
in message ...

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

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" wrote
in message ...

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

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

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" wrote
in message ...

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