yogi_Count Names In B2:Z For Last Specified Number Of Occupied Columns In Row 1

Google Spreadsheet   Post  #2289

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Nov-15-2017

question by: Tobiah81

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/xnySvk7_bto;context-place=forum/docs

A auto changing range in formula
Hey,

I'm trying to create a COUNTIF with a changing range.

I have a sheet with in theory infinite columns and 5 rows. A1 is empty. 

So the data starts from B1:B5.

Now i want to have a =COUNTIF(RANGE,"Marc") where the range is changing based on the last column used.

So right now the range would be B1:J5 (because column J is the last used).

But if i enter any data in column K the formula would have to change to B1:K5

How can i determine the last column that has any value in it?

I'm using >> =INDEX(1:1, MATCH(99^99,1:1, 1)) << but i get the value returned, not the column name

Some searching on google gave me this >> =CHAR(65+MATCH(MAX(B1:1),B1:1,0)) << but this means at column AA this wont work anymore.

On top of that, this formula should be used in the COUNTIF formula

For example => =COUNTIF(B1:CHAR(65+MATCH(MAX(B1:1),B1:1,0))12;"Marc")

Part 2 of the changing range is that "B1" isn't the start. It should be "the last column used -5"

So in this example it should be Column "J-5" => Column F.

Is this even possible?

Hope this makes any sense