How to count blanks in a row, after first Non Blank cell of that Row

1
votes

For instance i have a range "B3:AY3" and first non blank cell that contains value is "R3", i need to count all blank cells after cell "R3"

3
excelexcel-formulaexcel-2010vba
Whats the difference between after the first or just count all subtract 1, to account for the first? =Countif(B3:AY3, "")-1 – user2140261
well there is difference mate, because range "B3:AY3" have a non continuous data, so as i wrote the first non blank cell is "R3" in this range & there is data available after "R3" as well, I need to count the blank cells after "R3"... and there are 74000 rows almost with different cells that contains First Value. – Tushar Varshney

3 Answers

0
votes

This formula must be array-entered:

A3: =COUNTBLANK(OFFSET(MyRange,0,MATCH( TRUE,LEN(MyRange)>0,0)-1,1,COLUMNS( MyRange)-MATCH(TRUE,LEN(MyRange)>0,0)+1))

where MyRange = B3:AY3

and fill down as needed

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

The above formula will return an error message if there are ONLY blank cells in the range, or if there are any cells in the range which contain an error. If that is a problem, use the following array-entered formula instead:

=IFERROR(COUNTBLANK(OFFSET(MyRange,0,MATCH(
TRUE,IFERROR(LEN(MyRange)>0,TRUE),0)-1,1,COLUMNS(
MyRange)-MATCH(TRUE,IFERROR(LEN(MyRange)>0,TRUE),0)+1)),COLUMNS(MyRange))

IT will count errors as being non-blanks, and count all the cells if all are blank.

0
votes

and first non blank cell that contains value is "R3"

I have taken the example for Row 1. Amend as applicable :)

Try This

=COUNTBLANK(INDIRECT(ADDRESS(1,MATCH(TRUE,LEN(A1:AY1)=0,0)) & ":AY1"))

Enter this as an Array Formula. i.e you have to press Ctrl + Shift + Enter instead of Enter

enter image description here

For 3rd row, the formula becomes

=COUNTBLANK(INDIRECT(ADDRESS(3,MATCH(TRUE,LEN(A3:AY3)=0,0)) & ":AY3"))

enter image description here

0
votes

If you only need it to work for row three then use the following: 

=COUNTBLANK(B3:AY3) - (MATCH(TRUE,INDEX((B3:AY3<>0),0),0)-1)

This is a regular formula and will not need to be entered as an array, also as I am not using any Volatile function (like INDIRECT and OFFSET) in should also be much better performance wise.

All I m doing is counting all blanks, then subtracting all the blanks before the first cell with a value.