Counting From Certain Cell Until Predetermined Column

[Hanan]

In general: I want to count the number of empty cells, starting from the last-non-empty cell, until a column that I determine.

For example: Assume I have 1 Row (A1:K1). All cells are empty, except for two cells, which contain the number "1" (cell C1 and cell E1). I want to count the number of empty cells from E1 until a column of my choice. For sake of the example, let's assume that I choose column number H.

A	B	C	D	E	F	G	H	I	J	K
		1		1			"end column"

<tbody>
</tbody>

The answer in this case needs to be "2".

Note: the last cell with a number, as well as the "end column" will change from time to time. So, the formula needs to change as well. As I wrote in the beginning, I want to count the number of empty cells between my "end-cloumn" and the first non-empty cell.

 

[Hanan]

To clarify and Simplify: I need a formula that says: "count backwards from column X, the number of empty cells, until the first non-empty cell".

Thanks.

 

[Jonmo1]

Welcome to the board.

How exactly will you 'choose' the end column?
Or more specifically, how can our formula recognize which column you've chosen?

Are you going to put a column Number or Letter in a cell?
Or put some keyword in Row1 of that chosen column?

 

[Hanan]

Thanks for the warm welcome.

I have a different worksheet which determines the number of the column I need to end at. So lets assume that it has determined that the "end-column" is column number 8 (which equals 8). But if tomorrow it determines that the "end column" is 9 (column I), it will need to change accordingly.

 

[Jonmo1]

Try this with the assumption that ALL cells are either Blank or contain the number 1.

=8-MATCH(2,A1:K1)-1

Where 8 is your arbitrarily chosen end column #
If you have another formula somewhere that works out that number, you can reference it as well

=Z1-MATCH(2,A1:K1)-1

Z1 = your formula to determine the end column number.

 

[Hanan]

GENIUS!
I was already on the verge of tears.

Thanks!!!!

 

[Jonmo1]

You're welcome.

 

[Hanan]

Clarification please: in the formula you gave "=8-MATCH(2,A1:K1)-1" what is the significance of the "2"? what does it mean? What I understood from the "match" formula explanation, is that it should represent the number I want to find in the cells, as if I was saying: "tell me how many cells in the range have the number 2". Obviously, I am wrong. Can you please explain?

 

[Aladin Akyurek]

If the possible entries in row 1 are numbers and A2 houses the column number the user inputs...

=A2-MATCH(9.99999999999999E+307,1:1)

If the entries in row 1 can be any value...

=A2-LOOKUP(9.99999999999999E+307,1/(1-ISBLANK(1:1)),COLUMN(1:1))

 

[Jonmo1]

what is the significance of the "2"? what does it mean?

As I said, my formula was taking the assumption that all cells in the range were either blank or 1.
So the significance of 2 is simply that it's larger than any numerical value in the range.

When the 3rd argument of Match is omitted, it's assumed to be 1
So it would be =8-MATCH(2,A1:K1,1)-1

When that option is 1, it means match looks for the 'largest value that is less than or equal to the lookup value'
And it's assumed that the range is sorted in ascending order.
Since all numers in the range are less than the lookup value, then there is no exact match.
It therefor returns the last numerical value that is less than the lookup value (the furthest number to the right).

Hope that clears it up.