Count Blank Cells Until Value is Found

Question

How can I find a count of blank cells until a value is found in a row?
I did some searching and found that I could use either
COUNTBLANK with INDIRECT or COUNTIF or MATCH with INDEX

But couldn't get it to work on any of them...

The formula should go into B5.
Example:

Thank you for the help.

Kaiser · Accepted Answer · 2019-07-12T03:06:29.467

4

Use MATCH() Function:

=MATCH(FALSE,ISBLANK(B1:F1),0)-1

That formula will give you the number of cells to the first non-blank cell, so if you substract one you’ll get the number of blank cells. As is an array formula confirm it with CRTL+SHIFT+ENTER instead of ENTER so you get the brackets {} in the formula:

{=MATCH(FALSE,ISBLANK(B1:F1),0)-1}

edited Jul 12 '19 at 03:06

answered Jul 12 '19 at 01:53

Kaiser

1,957
1
19
28

1

I'm trying to understand the formula but either way I get an error #N/A. So I changed it to something like `=MATCH(FALSE,INDEX(B1:F1="",),0)-1` – ggmkp Jul 12 '19 at 02:00
1

The formula that @Kaiser has suggested needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. – Domenic Jul 12 '19 at 02:21
Thanks Domenic for claryifing, I’ve updated the answer. – Kaiser Jul 12 '19 at 02:39
I believe my formula is better because it doesn't use array... but thanks! – ggmkp Jul 12 '19 at 19:01
1

Rather an array than a volatile function – JvdV Jul 12 '19 at 19:04

score 0 · Answer 2 · answered Jul 12 '19 at 02:33

0

Please try this basic Lookup function. (This is normal function which means no need to confirm CTRL+Shift+Enter.)

This below function needs to be written on A1 then you can drag it down.

=LOOKUP(2,1/($A1:G1<>""),COLUMN($A1:G1))-2

answered Jul 12 '19 at 02:33

Mertinc

793
2
13
27

JvdV · Answer 3 · 2019-07-12T06:20:32.377

0

You can use a combination of MATCH and MAX (or MIN) for this:

Formula in B5:

=MATCH(MAX(B1:G1),B1:G1,0)-1

Drag down

edited Jul 12 '19 at 06:20

answered Jul 12 '19 at 06:13

JvdV

70,606
8
39
70

1

Then specify your questions better. It works on what you told us. – JvdV Jul 12 '19 at 19:03

Count Blank Cells Until Value is Found

3 Answers3