Home |
Search |
Today's Posts |
#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 -- jmg092548 ------------------------------------------------------------------------ jmg092548's Profile: http://www.excelforum.com/member.php...o&userid=26119 View this thread: http://www.excelforum.com/showthread...hreadid=394467 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
I need a formula to sum column b if column a is between two dates | Excel Discussion (Misc queries) | |||
I need a formula to sum column b if column a is between two dates | Excel Discussion (Misc queries) | |||
how to enter a formula using column() function for a range | Excel Worksheet Functions |