Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with a formula 1

Status
Not open for further replies.

mearmortal

Programmer
May 1, 2002
79
GB
I use this formula: -

=COUNTIF('Games'!C1:C46,"Europe ")
But the end cell number changes based upon it being the last empty cell, calculated from this formula: -

=MATCH("Y",'Games'!F:F,0)-1
This formula reports the row number in column F with a Y in it and returns the value -1. Hence the last empty cell.

What I want to do is substitute the value generated from the second formula with the value in the first that reports 46 above.

I can't see how to do it, can I have someones assistance please.

 
Hi,

Check out faq68-1331 for a different approch for dynamic ranges that does not need a "Y" in the last row.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I changed the formula in the one cell (H11) to: -
="'Games'!C1:C"&MATCH("Y",'Games'!F:F,0)-1

And this formula in another Cell to: -
=COUNTIF(INDIRECT(H11,TRUE),"Europe ")

I then changed the formula to 1 cell: -
=COUNTIF(INDIRECT(("'Games'!C1:C"&MATCH("Y",'Games'!F:F,0)-1),TRUE),"Europe ")

And it worked like a treat. Thanks for all your efforts.
Much Appreciated.
 
Assuming that column F has a value in every row in your table, would this not also work, forgetting the "Y" thing?
[tt]
=COUNTIF(INDIRECT(("'Games'!C1:C"&COUNTA('Games'!F:F)),"Europe ")
[/tt]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I can see where you are going with this, but new items on the list do not have a Y in column F therefore, I need to know how many of them do not without having to manually count them.
Also this value has to change automatically to ensure accuracy.

Thanks for everything
 
new items on the list do not have a Y in column F therefore, I need to know how many of them do not without having to manually count them."

Really?
[tt]
=COUNTIF('Games'!F:F),"<>Y")
[/tt]
or
[tt]
=COUNTA('Games'!F:F))-COUNTIF('Games'!F:F),"Y")
[/tt]
Did I misunderstand your question?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top