Count empty blank cells betwen numbers - Help Please

S

shaunkaz

Board Regular

Joined

Jan 30, 2008

Messages

204

• Nov 20, 2014

• #2

dont know why but it doesnt show the table.. the 2 under the 2 means there are 2 empty blank cells the the left of the top 2, 1 empty cell to the left of number 5 and 3 empty cells to the left of number 1.

Thanks
Shaun

 

Upvote 0

adam087

Well-known Member

Joined

Jun 7, 2010

Messages

1,356

• Nov 20, 2014

• #3

Perhaps like this

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	1			2		5				1
2				2		1				3
Sheet1

Cell Formulas
Range	Formula
B2	=IF(B1="", "", COUNTBLANK($A$1:B1)-SUM($A$2:A2))

/AJ

 

Upvote 0

S

shaunkaz

Board Regular

Joined

Jan 30, 2008

Messages

204

• Nov 20, 2014

• #4

Excellent, Thankyou very much

Kind Regards
Shaun

 

Upvote 0

adam087

Well-known Member

Joined

Jun 7, 2010

Messages

1,356

• Nov 20, 2014

• #5

No probs. Thanks for the feedback.

/AJ

 

Upvote 0

S

shaunkaz

Board Regular

Joined

Jan 30, 2008

Messages

204

• Nov 20, 2014

• #6

Hi to try this I copied your table and it worked when I tried to on my sheet because and have my B row further down how can I modify this formula to work.. I tried =if(E150="",,COUNTBLANK($E$5,E150)-SUM($E$5:E5)) but wont work as you see my information is on row 5 but formula is on row 150

Thanks
Shaun

 

Upvote 0

adam087

Well-known Member

Joined

Jun 7, 2010

Messages

1,356

• Nov 20, 2014

• #7

Basically the COUNTBLANK reference should refer to the Row where the blanks you wish to count are. The first reference (with the $ signs) is where on that row you wish to start counting, and the second part, after a colon not a comma, is the next cell along (to start with), which will change as you drag along.

The SUM reference should refer to the row where you're inputting the formula and the first reference, with $ signs should be the first cell on that row (which you would leave blank) and the next reference, after the colon, is the same cell.

/AJ

 

Upvote 0

S

shaunkaz

Board Regular

Joined

Jan 30, 2008

Messages

204

• Nov 21, 2014

• #8

I have now been asked to slight modify the formula.. I need to add 1 (+1) to each of the answers above. Can someone please tell me how this can be done..

Thanks
Shaun

 

Upvote 0

adam087

Well-known Member

Joined

Jun 7, 2010

Messages

1,356

• Nov 21, 2014

• #9

Sure. To edit my formula above it would become...

Rich (BB code):

=IF(B1="", "", COUNTBLANK($A$1:B1)-SUM($A$2:A2)+1)

The filled to the left. Change the references to suit

/AJ

 

Upvote 0

S

shaunkaz

Board Regular

Joined

Jan 30, 2008

Messages

204

• Nov 21, 2014

• #10

Hi Adam

Thanks for your reply, it work on the first number but not the rest.. Am I doing something wrong

Thanks
Shaun

 

Upvote 0