Identify a dynamic range to then be used in another formula

Topic

Hello,

I have a spreadsheet that contains dates in row 4, specifically D4:BD4.

The same spreadsheet contains manually entered text data in row 45, specifically D45:BD45.

Cell BF3 contains a date what will be used to create a range to count specific data from row 45.

Cell BF45 should contain the results.  I want to count the number of cells that DO NOT contain either “NO GAME” or are blank in a range that stretches from D45 to whichever column contains the exact or next earliest date when compared to cell BF3.

Can someone provide some guidance on how I could accomplish this, preferably without using VBA.

Thanks so much!

Replies

This works for me.

=COLUMNS($D45:$BD45)-COUNTIF($D45:$BD45,"")-COUNTIF($D45:$BD45,"NO GAME")

It counts the number of columns and subtracts all instances of “NO GAME” and blank.

cheers, Paul

Thanks, Paul, I like the simplicity of that.

My issue with this solution is that it will always look thru ALL cells within the master range (D45:BD45).  There are time that I need to look back at a shorter window of time.  I would like to use the date in cell BF3 determine what the end cell of the range should be.

For example, if cell BF3 contains ’05/10/2025′ the formula should be using the range D45:V45.

Is this something that is possible?

 

I’m thinking a UDF is the solution, but I don’t have Excel to play with. Will have more think…

cheers, Paul

Some thinking later..

=COUNTIF(INDIRECT("D45:"&ADDRESS(45,MATCH(BF3,D4:BD4,1)+3)),"NO GAME")

We use “match” to find the column that nearest matches the date entered, “address” to give us the A1 format and then “indirect” to return the range.

cheers, Paul

1 user thanked author for this post.

BigDaddy07

Great Job! That worked as desired.

To shrink the whole formula and make editing easier we can use LET.

=LET(Rng, "D45:"&ADDRESS(45,MATCH(BF3,D4:BD4,1)+3,4), COLUMNS(INDIRECT(Rng))-COUNTIF(INDIRECT(Rng), "")-COUNTIF(INDIRECT(Rng), "NO GAME"))

With LET we define a variable “Rng” to be equal to the calculated range, rather than calculate it 3 times, then use the variable name in the equations.

cheers, Paul

And if we use COUNTIFS, we can make it even better because we don’t need to count the number of columns. 

=LET(Rng, "D45:"&ADDRESS(45,MATCH(BF3,D4:BD4,1)+3,4), COUNTIFS(INDIRECT(Rng), "<>", INDIRECT(Rng), "<>NO GAME"))

Not sure I can make it shorter…

cheers, Paul

Yes I can, we don’t need the return type in ADDRESS. Saves 2 characters.

=LET(Rng, "D45:"&ADDRESS(45,MATCH(BF3,D4:BD4,1)+3), COUNTIFS(INDIRECT(Rng), "<>", INDIRECT(Rng), "<>NO GAME"))

cheers, Paul