# Counting From Certain Cell Until Predetermined Column

#### Hanan

##### New Member
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.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.

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?

Last edited:
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.

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.

Last edited:
GENIUS!
I was already on the verge of tears.

Thanks!!!!

You're welcome.

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?

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))

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.

Replies
7
Views
384
Replies
0
Views
72
Replies
3
Views
52
Replies
0
Views
259
Replies
12
Views
271

1,211,789
Messages
6,103,986
Members
447,888
Latest member
eaydogan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back