Diagonal Cell Count in Excel

Question

I need to search diagonally and return the cell count until the first blank cell is reached. The following formula works perfect for doing this function in a contiguous row left to right: =MATCH(1,--(A1:AB1=""),0)-0).

I use this formula to search right to left in a contiguous row: =MAX(IF(LEN(A1:AB1)<>-1,COLUMN(A1:AB1)))-MAX(IF(LEN(A1:AB1)=0,COLUMN(A1:AB1)-1))

My problem is coming up with a similar function that will do this with diagonal, non-contiguous cells (and also from right to left). I need to search down to the left corner of a range and also separately search down to the right corner of a range (two different formulas). For example, in the table below, if I started counting down to the left from cell C1 the return should be 2 (counting cells B2, A3, with A3 being the first blank cell) . If I started counting from cell C1 down to the right the return should be 3 (counting cells D2, E3, F4, with F4 being the first blank cell) .

I will put these formulas in columns far to the right of the table and copy the formula down to get the same results for C2, C3, C4, & D2, D3, D4, & E2, E3, E4, etc.. I will have enough blank columns to prevent the formula from becoming a circular reference.

	A	B	C	D	E	F
1		21	11		25	2
2	44	19	15	32	16	9
3		7	22	2	13	16
4	5	11	8	17	10
5	26		19		4	6

My spreadsheet is around 60 columns by 200 rows and need to have a formula that can be copied and dragged.  I know very little VBA, so a formula is what I am looking for, but will use VBA if it can be easily explained.

I have spent hours, days really, looking for a solution. Any help would be immensely appreciated. Thanks

*Note: I also posted this question here. I will post the solution to both forums when it is reached.

Answer 1

I have figured this out on my own. It's a very simple, but unwieldy, solution that works for both searching diagonally down to the left or to the right, or will work for any non-contiguous cells. For my actual spreadsheet, the solution for searching diagonally down 10 cells to the right is this.

Formula:  

=MATCH("",CHOOSE({1,2,3,4,5,6,7,8,9,10},AN12,AO13,AP14,AQ15,AR16,AS17,AT18,AU19,AV20,AW21),0)-1

This will return the cell count to the first blank cell in the sequence of cells provided in the formula. It could obviously be modified to find whatever your looking for and count how many cells it takes to get there. I need to search at least 30 cells, so my actual formula is very long, but it works. Yeah!

Answer 2

Yes, that works! I just didn't have the brain power to follow that solution through multiple nestings.

I also just found (literally within minutes of you giving yours) an alternate solution using the MATCH and CHOOSE functions. (See my reply below)

Thanks for providing an answer.  It's great that there can be multiple options in Excel for coming to the same conclusion!

Answer 3

You are right use Count with a nested if and it works. This is formula for diagonal for C1 to left

=IF(COUNT(C1),IF(COUNT(B2),IF(COUNT(A3),3,2),1),0)

I suggest you don't try entering it all in one go. I did it one step at a time, i.e

=IF(COUNT(C1),1,0)

Then replace the 1 2 etc with the next IF statement, i.e

=IF(COUNT(C1),IF(COUNT(B2),2,1),0)

Clunky - yes but it works

Just thought - the length of the expression may get too long, in which case you will have to do it in steps over a few cells.

Answer 4

Thanks for the reply. Yes, I have tried variations of the COUNT function. Unfortunately, while it will return the number of blank cells, it won't tell me how many cells, in a diagonal row, there are until the first blank cell is reached. At least not in any way that I can figure out. That is what I am after. 

I will take a formula that I have to modify for each column, as long as I can drag it down. I need one formula each that looks diagonally down to the left and one that also looks diagonally down to the right for at least 30 cells. There shouldn't ever be any more than that before a blank cell is reached. I can modify each column to look at less cells when it reaches the border of the table, or have empty buffer columns to prevent a circular reference.

I think the COUNT function could work within a bunch of nested IF or other logical functions, but it gets unwieldy and clunky very quickly and making it stop counting once it finds the first empty cell is my dilemma if the IF function goes on for 30 cells for more.

But I'll take clunky and ugly if that will get me there.

Answer 5

Have you tried the Count function ?

For example =COUNT(C1,B2,A3) will return 2

You can drag it down and across. If you drag across it becomes =COUNT(D1,C2,B3)

to which you will have to edit to =COUNT(D1,C2,B3,A4)

Then when you drag down its fine.