Increment Cell by one: 2 conditions

[Irish Guy]

I am looking to increment Column A by one, if the corresponding cell in Column B has text.

It gets tricky as shown in the picture attached / linked at row 9 and 10. B9 and B10 have no text, so A9 and A10 are not incremented.

The next column with text is B11, so that value in A11 is 9.

I should clarify that all cells in Column B have formulas, even the blank ones.

Is there a formula that could do this automatically?

Thanks guys.


excel condition by Irish_Guy2012, on Flickr

http://farm6.staticflickr.com/5442/9298869397_39f53f5f31_o.png

 

[Rick Rothstein]

I am looking to increment Column A by one, if the corresponding cell in Column B has text.

It gets tricky as shown in the picture attached / linked at row 9 and 10. B9 and B10 have no text, so A9 and A10 are not incremented.

The next column with text is B11, so that value in A11 is 9.

I should clarify that all cells in Column B have formulas, even the blank ones.

Is there a formula that could do this automatically?

Thanks guys.


excel condition by Irish_Guy2012, on Flickr

http://farm6.staticflickr.com/5442/9298869397_39f53f5f31_o.png

Try this... put a 1 in A1 and then put this formula in A2 and copy it down...

=IF(B2="","",1+LOOKUP(999999,A$1:A1))

 

[JoeMo]

In A1 and copy down:
=IF(B1="","",COUNTA(B$1:B1))

 

[Irish Guy]

Try this... put a 1 in A1 and then put this formula in A2 and copy it down...

=IF(B2="","",1+LOOKUP(999999,A$1:A1))

Amazing, this works, thanks!

 

[Irish Guy]

Try this... put a 1 in A1 and then put this formula in A2 and copy it down...

=IF(B2="","",1+LOOKUP(999999,A$1:A1))

Would it possible to have a zero in the blank cells?

The reason I asked is that in another cell, a formula links to these cells and carries out a sum. Because the cell is blank it displays #VALUE! which disrupts other formulas.

If there was a way of deleting this, or having a zero in the blank cells it would be great

 

[Irish Guy]

Would it possible to have a zero in the blank cells?

The reason I asked is that in another cell, a formula links to these cells and carries out a sum. Because the cell is blank it displays #VALUE! which disrupts other formulas.

If there was a way of deleting this, or having a zero in the blank cells it would be great

Ah, I figured it out...

=IF(ISERROR(N10/M10),"",N10/M10)

 

[Rick Rothstein]

Would it possible to have a zero in the blank cells?

The reason I asked is that in another cell, a formula links to these cells and carries out a sum. Because the cell is blank it displays #VALUE! which disrupts other formulas.

Just change my formula to output 0 instead of "" when the cell in Column B is empty...

=IF(B2="",0,1+LOOKUP(1000000,A$1:A1))