Using =IF(B10<>"",COUNTA($B$10:B10),"") to number rows in columns other than A

[Burbank Frank]

I tried out this formula, =IF(B10<>"",COUNTA($B$10:B10),""), to number the rows in a column that have data in adjacent columns, but leave a row un-numbered and uncounted if the adjacent cell was blank. It worked great in Column A.

However, I tried using the formula in column D and it didn't work so well. Instead of numbering in sequence it was counting by 3's. 3, 6, 9, 12 etc. It still skipped the rows with no data in the adjacent cells but the numbering was wrong.

I tried making some changes no no avail.

This is the formula in D10: =IF(E10<>"",COUNTA($B$10:E10),"")

Any ideas on how to make this formula work in Column D?

Frank

 

[alow]

Frank,

I think your problem is with your formula having a static reference for the first part of the array in the COUNTA (The dollar signs). Since you have the reference in the original location as $B$10:B10, when you copied it over, it kept the reference so your new location is counting across columns B through E. You can fix this 1 of 2 ways. You can modify only the formula in column D to read =IF(E10<>"",COUNTA($E$10:E10)"").

Alternatively, you could change your original formula and recopy it. The dollar signs will keep the reference they are in front of static, so if you changed your original formula to =IF(B10<>"",COUNTA(B$10:B10),"") and copied it, the column label would change relatively.

The final recommendation I'm going to give is a new formula that I think gives you the same result but with easier formulas.

=IF(ISBLANK(B10),ROW(),"")

This will give a true/false value based on whether or not the targeted cell is blank, and then return the current row number. Let me know if you need any further assistance!

 

[Burbank Frank]

Frank,

I think your problem is with your formula having a static reference for the first part of the array in the COUNTA (The dollar signs). Since you have the reference in the original location as $B$10:B10, when you copied it over, it kept the reference so your new location is counting across columns B through E. You can fix this 1 of 2 ways. You can modify only the formula in column D to read =IF(E10<>"",COUNTA($E$10:E10)"").

Alternatively, you could change your original formula and recopy it. The dollar signs will keep the reference they are in front of static, so if you changed your original formula to =IF(B10<>"",COUNTA(B$10:B10),"") and copied it, the column label would change relatively.

The final recommendation I'm going to give is a new formula that I think gives you the same result but with easier formulas.

=IF(ISBLANK(B10),ROW(),"")

This will give a true/false value based on whether or not the targeted cell is blank, and then return the current row number. Let me know if you need any further assistance!

Alow, I figured out the Absolute Cell Reference problem myself and adjusted the original formula to correct for it's location in the spreadsheet. I haven't tried your alternate formula yet but thanks for offering it.

Addendum: I just tried your alternate formula in my test spreadsheet. Unfortunately the only rows that got numbered were the ones with adjacent empty spaces (exactly opposite of what I was going for).

Frank