row countif() that allows inserting new columns....?

[abrogard]

I've got a countif(b2:k2,"y") and I keep adding new columns so that it becomes countif(b2:l2,"y") and then countif(b2:m2,"y") and so on...
I can't find a way to write it so's I don't have to change it every time I insert a new column. Can anyone help? regards, abrogard@yahoo.com (please write me if you can help)

[Jay Petrulis]

Hi,

You can name your Row 2 range and give it a dynamic formula so it will automatically update.

Insert>Name>Define
(I called it ColsUsed)

Refers to
=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$B$2:$IV$2))

Change Sheet1 to the name of your worksheet.

Hit OK.

Now your function can be

=COUNTIF(ColsUsed,"y")

EDIT: This assumes that there are no gaps in your data. If there ar, you will have to use another method to find the last used column in a worksheet function. Search the board for posts by Aladin Akyurek and Colo for great solutions.

[ This Message was edited by: Jay Petrulis on 2002-05-26 09:09 ]

[stone]

On 2002-05-26 08:57, abrogard wrote:
I've got a countif(b2:k2,"y") and I keep adding new columns so that it becomes countif(b2:l2,"y") and then countif(b2:m2,"y") and so on...
I can't find a way to write it so's I don't have to change it every time I insert a new column. Can anyone help? regards, abrogard@yahoo.com (please write me if you can help)

try THIS
=countif(INDIRECT("b2:k2","y"))

Hope it helps

BK
Stulli

[Chris Davison]

On 2002-05-26 09:10, stone wrote:

try THIS
=countif(INDIRECT("b2:k2","y"))

Hope it helps

BK
Stulli

Stulli



gotta test those new formulas before you post !

=countif(INDIRECT("b2:k2"),"y")

Chris

[Aladin Akyurek]

Stulli & Chris,

That COUNTIF formula with INDIRECT doesn't meet the requirements, Jay's does although with a risky (volatile) COUNTA in it.

Aladin

[Chris Davison]

Aladin,

I'm struggling to try to make my method fail.....

what's wrong with it ?

ta
Chris

[Chris Davison]

ahhh...

he *wants* it to update the range, not stay static....

I read it wrong !

[eliW]

Aladin, Chris etc.
Maybe I am missing something here?
If the poster wants to add columns and apply his formula to the entire range, then why not include in the formula the entire row range from the beginning, or at least : =COUNTIF(B2:IV2,"y")
I am missing something certainly !?
Eli

[Chris Davison]

Hi Eli,

probably stuff outside that initial range that they don't want counted (maybe the countif formula is on the same row too - circular reference error) : I guess we'll soon find out !

[Aladin Akyurek]

On 2002-05-26 11:03, eliW wrote:
Aladin, Chris etc.
Maybe I am missing something here?
If the poster wants to add columns and apply his formula to the entire range, then why not include in the formula the entire row range from the beginning, or at least : =COUNTIF(B2:IV2,"y")
I am missing something certainly !?

Eli,

The only possible objection to

=COUNTIF(B2:IV2,"y") [1]

would be that COUNTIF is forced to lots of unused cells, also like in

=COUNTIF(A:A,"y") [2] or

=COUNTIF(A2:A65536,"y") [3]

A caveat regarding these formulas is that the referenced area does not have any other data but the target data to which the COUNTIF formula is applied.

Jay's OFFSET formula is also subject to the same caveat. Since COUNTIF and COUNTA will have comparable performance, your proposal can very well be sufficient.

However, if COUNTIF is going to be applied to many rows below row 2, it could be better to compute the actual range in use by means of a separate formula:

Supposing that row 1 has labels for every column of data, I'd compute in a separate worksheet (Admin, for example):

=MATCH(REPT("z",25),Sheet1!1:1)-(CELL("Col",Sheet1!$A$2)-1)

and name the cell of this formula Ncols, then use:

=COUNTIF(OFFSET(Sheet1!$B2,0,0,1,Ncols),"y")

Copying down this could be attractive. One needs of course to verify all this before any committment in case there is a performance problem.

Aladin