How to refer to current column in a formula?

[jmg092548]

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 i
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 hav
to explicity say

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

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

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

Thanks in advance for any help!

Jim Guinness
Eastern Massachusettts, US

 

[Max]

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

 

[Biff]

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]

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 wil
be
A10:A50
If the formula is entered in E8 then the Countif range will b
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 a

text also. It works that way (surprisingly), but also works without, s
I'll not use them.

Did you post your reply from Excelforum? If so, is there an easy wa

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

Thanks again! -- Jim

Bif

 

[Biff]

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