Counting From Certain Cell Until Predetermined Column

Hanan

New Member
Joined
Feb 25, 2016
Messages
7
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.


ABCDEFGHIJK
11"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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Hanan

New Member
Joined
Feb 25, 2016
Messages
7
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

Hanan

New Member
Joined
Feb 25, 2016
Messages
7
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

You're welcome.
 

Hanan

New Member
Joined
Feb 25, 2016
Messages
7
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top